0

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?

Improve this question
1
  • 2
    Any chance you fix the design? The fact that you have violated 1NF by storing delimited lists of data is the real issue. The example you posted should be three rows, not a 1 row with three values. Commented Oct 31, 2018 at 20:47

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

That did it. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.