Filter by Comma Separated Values in IN Clause ?

The Customer table has ‘PrimaryCode’ and ‘OtherCode’ columns store Code from Codes table:Image

However some of the codes store in Customer table do not exist in Codes table.

I need to let Codes table left join Customer table, this is the first version SQL:

select * from Codes a
left outer join Customer b
on a.CodeID in (b.PrimaryCode)

That did not give me the correct outcome as query above miss out the values in OtherCodes column, so I try this:

select * from Codes a
 left outer join Customer b
 on a.CodeID in (CAST(b.PrimaryCode as varchar) + ',' + b.OtherCodes)

I try to concat the PrimaryCode + OtherCodes to have a clause like this: IN(‘1,2,3,4,5’), but that does not seems right, and I get the error message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ‘23,24,26,27’ to data type int.

Ok, now I know that is not right doing that, there are few solutions provided in internet (search from Google of course!). The final solution would be using LIKE keyword, then I have this which work perfect:

select * from Codes a
left outer join Customer b
on ',' + Cast(b.PrimaryCode as varchar) + ',' + b.OtherCodes + ',' LIKE '%,' + Cast(a.CodeID as varchar) + ',%'

The result I want:

Image

 

Hints to search in Google: Comma separated values in IN Clause

Keywords: LIKE

Reference: http://stackoverflow.com/questions/4613273/sql-use-comma-separated-values-with-in-clause

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s