Обсуждение: Check if column is substring of another column
PostgreSQL 8.1 question:
I have two columns.
policyNumber contains a 12-13 varchar string
AllPolicyNumbersIncluded contains one or more 12-13 varchar strings (policy nums) separated by commas
I want to check if policyNumber is contained in AllPolicyNumbersIncluded.
In SQL Server the PATINDEX function returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types, so something like this works:
SELECT me.policyNumber, me.CompanyName, me.Address, PolicyPrint.AllPolicyNumbersIncluded
FROM PolicyPrint INNER JOIN PolicyDetails me
ON (PolicyPrint.cicPolicyNumber = PolicyDetails.policyNumber
OR PATINDEX('%' + me.policyNumber + '%',cicPrint.AllPolicyNumbersIncluded)> 0 )
Is there a way to do this in a single SQL statement in PostgreSQL 8.1?
Thanks,
Keaton
I have two columns.
policyNumber contains a 12-13 varchar string
AllPolicyNumbersIncluded contains one or more 12-13 varchar strings (policy nums) separated by commas
I want to check if policyNumber is contained in AllPolicyNumbersIncluded.
In SQL Server the PATINDEX function returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types, so something like this works:
SELECT me.policyNumber, me.CompanyName, me.Address, PolicyPrint.AllPolicyNumbersIncluded
FROM PolicyPrint INNER JOIN PolicyDetails me
ON (PolicyPrint.cicPolicyNumber = PolicyDetails.policyNumber
OR PATINDEX('%' + me.policyNumber + '%',cicPrint.AllPolicyNumbersIncluded)> 0 )
Is there a way to do this in a single SQL statement in PostgreSQL 8.1?
Thanks,
Keaton
On Wed, Jan 21, 2009 at 5:44 PM, Keaton Adams <kadams@mxlogic.com> wrote: > Is there a way to do this in a single SQL statement in PostgreSQL 8.1? SELECT d.policyNumber , d.CompanyName , d.Address , p.AllPolicyNumbersIncluded FROM PolicyPrint p INNER JOIN PolicyDetails d ON ( p.cicPolicyNumber = d.policyNumber OR p.AllPolicyNumbersIncluded LIKE '%' || d.policyNumber || '%' ) -- OR -- SELECT d.policyNumber , d.CompanyName , d.Address , p.AllPolicyNumbersIncluded FROM PolicyPrint p INNER JOIN PolicyDetails d ON ( p.cicPolicyNumber = d.policyNumber OR d.policyNumber = ANY(string_to_array(p.AllPolicyNumbersIncluded, ',')) )
Keaton Adams wrote: > PostgreSQL 8.1 question: > > I have two columns. > policyNumber contains a 12-13 varchar string > AllPolicyNumbersIncluded contains one or more 12-13 varchar strings (policy nums) separated by commas Rodrigo has given a direct answer, but you might want to consider either an array of text for AllPolicyNumbersIncluded or better still a join to another table. That will make your queries more natural. -- Richard Huxton Archonet Ltd