April 11, 2014

Query Comma Separated Values in Sql

Here is a situation where I will need to select and compare values from a table in sql server which stores data in a comma separated list. Well selecting data is not a problem but we want to run a query and compare the values within the list. In my scenario, the list contains numbers only but the solution will work for both numbers and strings. One solution will be to compare using LIKE and then check if the number exist but this won't necessarily be correct as ,2, and ,22, and 12, might all return the same result and the list does not contain commas at the start or at the end.

So, to solve this problem, I could have got the values in my .NET application and convert the numbers to a list and then perform comparison but I wanted to do it in sql. So, what I did was selected the column value and then added commas at the start and at the end and then perform the LIKE operation in the where section. Here is the sql script on how I did it.

Sql Script
  1. select * FROM
  3. (SELECT ',' + Tags + ',' as tagid --adding commas at start & end
  4. FROM [Employee].[dbo].[Table_2]) as src
  6. where src.tagid LIKE '%,2,%' --place your where clause


Reference: Shahed Kazi at AspNetify.com