CODEWITHSUNDEEP
sql-serversql-server-2008
user2363207
user2363207

Reputation:

SQL Server - Find out if string returned in subquery contains another string

I have two tables. One has a separate rows for each ID. The other has a string with a comma separated list of IDs. I'm trying to find out if the ID from the first table appears anywhere within the string of comma separated IDs in the second table.

Here's a sample (non-working) query:

select * from 
(select 'b' as ID) table1
where table1.ID in 
(select 'a,b,c' as CSV_LIST)

This is not how IN works, of course, but I don't know how else to approach this.

I've thought about using STRING_SPLIT() but it doesn't work in this version of SQL Server. I've also thought about using CONTAINS() but I can't seem to get it to work either.

Any ideas?

Upvotes: 0

Views: 1140

Answers (1)

S3S
S3S

Reputation: 25112

You can use LIKE or a custom string splitter like Jeff Moden's if you can't fix the design.

select table1.*
from table1
inner join table2
on table2.csv like '%' + table1.b + '%'

Note, this isn't SARGable because of the leading % so as Sean pointed out, fixing the design would be best, followed by another split function that doesn't use a WHILE loop.

Upvotes: 2

Related Questions