Check if column is substring of another column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Check if column is substring of another column

Author

Topic

inibhreaslain
Starting Member

19 Posts

Posted - 2008-05-29 : 17:26:22

I have two columns.
policyNumber contains a 12-13 varchar string
AllPolicyNumbersIncluded contains one or more 12-13 varchar strings (policy nums) seperated by commas

I want to check if policyNumber is contained in AllPolicyNumbersIncluded?

I have policyNumber LIKE AllPolicyNumbersIncluded which works when only one policy number is in AllPolicyNumbersIncluded and incidently works switched around AllPolicyNumbersIncluded LIKE policyNumber I assume because they are equal.

Can anyone tell me how to check if one column's value is a substring of another - without going through every possible substring of the second

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 17:31:52

select *
from table1
where ',' + AllPolicyNumbersIncluded + ',' LIKE '%,' + policyNumber + ',%'

E 12°55'05.25"
N 56°04'39.16"

inibhreaslain
Starting Member

19 Posts

Posted - 2008-06-02 : 11:44:56

Thanks Peso,
but that still just brings back results with only one policy number in the AllPolicyNumbersIncluded column.

Examples:

1) A row with
policyNumber: 'FM12345'
AllPolicyNumbersIncluded: '123xyz, 987abc'
should cause the return of the following three polciy's rows:
FM12345, 123xyz, 987abc

2) A row with
policyNumber: 'FM12345'
AllPolicyNumbersIncluded: '123xyz'
should cause the return of the following two polciy's rows:
FM12345, 123xyz

Only rows like example (2) are being returned.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 11:56:08

quote:
Originally posted by inibhreaslain

Thanks Peso,
but that still just brings back results with only one policy number in the AllPolicyNumbersIncluded column.

Examples:

1) A row with
policyNumber: 'FM12345'
AllPolicyNumbersIncluded: '123xyz, 987abc'
should cause the return of the following three polciy's rows:
FM12345, 123xyz, 987abc

2) A row with
policyNumber: 'FM12345'
AllPolicyNumbersIncluded: '123xyz'
should cause the return of the following two polciy's rows:
FM12345, 123xyz

Only rows like example (2) are being returned.

Does this mean it should return any policy numbers having atleast 1 character of AllPolicyNumbersIncluded field?
Do the above 2nd case return this policy also if present?
cdefg1

inibhreaslain
Starting Member

19 Posts

Posted - 2008-06-02 : 12:01:30

Apologies. I'm returning the policy itself with another part of the query. I just need to get rows that have the entire string of the policyNumber column in the AllPolicyNumbersIncluded columns.

policyNumber xyz123 would be returned for rows with AllPolicyNumbersIncluded = abc987, xyz123
AllPolicyNumbersIncluded = xyz123, abc987
AllPolicyNumbersIncluded = xyz123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 12:07:48

quote:
Originally posted by inibhreaslain

Apologies. I'm returning the policy itself with another part of the query. I just need to get rows that have the entire string of the policyNumber column in the AllPolicyNumbersIncluded columns.

policyNumber xyz123 would be returned for rows with AllPolicyNumbersIncluded = abc987, xyz123
AllPolicyNumbersIncluded = xyz123, abc987
AllPolicyNumbersIncluded = xyz123

Still not quite clear. So you will be having both Policynumber and AllPolicyNumbersIncluded in your tables. SO what will user be inputting? Or do you mean you need to compare Policynumber against AllPolicyNumbersIncluded and return the row if its within AllPolicyNumbersIncluded ?

inibhreaslain
Starting Member

19 Posts

Posted - 2008-06-02 : 13:06:20

The user does not input anything.
I want to compare the value stored in the policyNumber column to see if it is all or psrt of the value in AllPolicyNumbersIncluded column.

Peso's query returned only if the value in AllPolicyNumbersIncluded column was the same as the value stored in the policyNumber column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 13:10:07

quote:
Originally posted by inibhreaslain

The user does not input anything.
I want to compare the value stored in the policyNumber column to see if it is all or psrt of the value in AllPolicyNumbersIncluded column.

Peso's query returned only if the value in AllPolicyNumbersIncluded column was the same as the value stored in the policyNumber column.

yup. I got it now. but still cant understand how

1) A row with
policyNumber: 'FM12345'
AllPolicyNumbersIncluded: '123xyz, 987abc'
should cause the return of the following three polciy's rows:
FM12345, 123xyz, 987abc

returns FM12345 row but wont return cfgh1 row(as it contains 1 which is a part of 123xyz)

inibhreaslain
Starting Member

19 Posts

Posted - 2008-06-02 : 14:25:47

Yeah. I've been silly! There's two tables which I've been trying to simplify into one for the forum's purposes, but messed it up and confused everyone more I think!!!!

Here's the deal: (original query)
SELECT me.policyNumber, me.CompanyName, me.Address, PolicyPrint.AllPolicyNumbersIncluded
FROM PolicyPrint INNER JOIN PolicyDetails me
ON PolicyPrint.cicPolicyNumber = PolicyDetails.policyNumber

This returns policy details (number, name, address) and related policy numbers for policies with a policyNumber in the PolicyPrint table.

I also want the details of policies contained in theAllPolicyNumbersIncluded column.

Adding OR ',' + cicPrint.AllPolicyNumbersIncluded + ',' LIKE '%,' + me.policyNumber + ',%' to the join:
SELECT me.policyNumber, me.CompanyName, me.Address, PolicyPrint.AllPolicyNumbersIncluded
FROM PolicyPrint INNER JOIN PolicyDetails me
ON (PolicyPrint.cicPolicyNumber = PolicyDetails.policyNumber
OR ',' + cicPrint.AllPolicyNumbersIncluded + ',' LIKE '%,' + me.policyNumber + ',%')

returns all I need except when there is more than one number in the AllPolicyNumbersIncluded. Then it leaves the related policies out.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 14:30:05

May be this

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 )

inibhreaslain
Starting Member

19 Posts

Posted - 2008-06-02 : 15:05:58

Thanks visakh. Exactly what I needed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 15:06:55

quote:
Originally posted by inibhreaslain

Thanks visakh. Exactly what I needed.

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources