Re: Equivalent of MSSQL "PATINDEX" ? - Mailing list pgsql-novice

From Jeff Eckermann
Subject Re: Equivalent of MSSQL "PATINDEX" ?
Date
Msg-id 20040406145117.45481.qmail@web20802.mail.yahoo.com
Whole thread Raw
In response to Equivalent of MSSQL "PATINDEX" ?  ("Manfred Koroschetz" <mkoroschetz@rkmus.com>)
List pgsql-novice
--- 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/

pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: 7.4 dramatically slower than 7.3? (was: snowflaking)
Next
From: Ron St-Pierre
Date:
Subject: Re: Formating Dates