Обсуждение: Equivalent of MSSQL "PATINDEX" ?
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:
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)
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 + '%'
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:
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
where patindex(c.ProdPattern,A.ProdID) = 1
order by C.ProdCat desc
Appreciating any help in advance,
Manfred Koroschetz
mkoroschetz@rkmus.com
mkoroschetz@rkmus.com
--- 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/