Postgres Pro
Downloads
Home   >   mailing lists

Thread: Check if column is substring of another column

Check if column is substring of another column

From
Keaton Adams
Date:
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

Re: Check if column is substring of another column

From
Rodrigo E. De León Plicet
Date:
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, ','))
    )

Re: Check if column is substring of another column

From
Richard Huxton
Date:
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