Skip site navigation (1) Skip section navigation (2)

Equivalent of MSSQL "PATINDEX" ?

From: "Manfred Koroschetz" <mkoroschetz(at)rkmus(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Equivalent of MSSQL "PATINDEX" ?
Date: 2004-03-30 20:59:39
Message-ID: 20040330T155939Z_B1B3000E0000@rkmus.com (view raw or flat)
Thread:
Lists: pgsql-novice
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,

Manfred Koroschetz
mkoroschetz(at)rkmus(dot)com

Responses

pgsql-novice by date

Next:From: Manfred KoroschetzDate: 2004-03-30 21:16:18
Subject: Help for MSSQL "Compute" equivalent in Postgres
Previous:From: Thilo HilleDate: 2004-03-30 12:52:25
Subject: Re: dynamic interval in plpgsql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group