Re: Equivalent of MSSQL "PATINDEX" ?

#1

--- Manfred Koroschetz <mkoroschetz (AT) rkmus (DOT) com> wrote:

Quote:

I am currently working on migrating an application
from a ASP/Microsoft SQL DB on Win2k,
into a PHP/Postgres 7.4.2 on Linux environment.
My experience with Postgres is not extensive, and I
have not been able to translate the following MSSQL
Query
into the equivalent Postgres form. The essence of
the query is as follows:

Find "ProdCat" (return only one (1) row = longest
match),
from a subset of rows ("C") of table ("B") for the
longest string match
at string position 1 (beginning of string) for
"ProdPattern" given a specific "ProductID"
(A.ProdID)

Notes: A.ProductID is the result of a subquery
B.Catalogs is the result of a subquery
C.ProdPattern = C.ProdCat + '%'

Examples: C.ProdPattern = '582%', A.ProdId =
'582125678765','583452430987', D.Catalogs = '12354'
Expected Result:

Original MSSQL Query:

select top 1 C.ProdCat from (select * from B where
B.CatalogID = D.Catalogs) as C
where patindex(c.ProdPattern,A.ProdID) = 1
order by C.ProdCat desc

Appreciating any help in advance,

Have a look in the docs, under "functions and
operators", especially "string functions and
operators" and "pattern matching".

Quote:

Manfred Koroschetz
mkoroschetz (AT) rkmus (DOT) com

__________________________________
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Equivalent of MSSQL "PATINDEX" ?

comp.databases.postgresql.novice comp.databases.postgresql.novice

Re: Equivalent of MSSQL "PATINDEX" ? - 04-06-2004 , 09:51 AM