Thread: Equivalent of MSSQL "PATINDEX" ?

--- Manfred Koroschetz <mkoroschetz@rkmus.com> wrote:
> 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".

>
> Manfred Koroschetz
> mkoroschetz@rkmus.com
>


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