Check if column is substring of another column

From: Keaton Adams <kadams(at)mxlogic(dot)com>
To: "\"pgsql-general(at)postgresql(dot)org\"" <pgsql-general(at)postgresql(dot)org>
Subject: Check if column is substring of another column
Date: 2009-01-21 22:44:45
Message-ID: C59CF36D.8771%kadams@mxlogic.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Igor Katson 2009-01-21 23:33:02 A complex plproxy query
Previous Message Richard Broersma 2009-01-21 21:15:18 Re: how to implement a foreign key type constraint against a not unique column