SQL Server 2012 – Using IN for Filtering

sql serversql-server-2012

I have the below table

Col1       Col2
1          ACA,ABS,PHR
2          PHR
3          MCM
4          ABC

Now I want to filter the data from this so if I have a filter parameter as say 'ABS,MCM' I want to get only the rows which have at least one matching code. So in this case I should get the filtered result as 

Col1       Col2
1          ACA,ABS,PHR
2          MCM

Now I can use the query Select * from myTable where Col2 in ('ABS','MCM') but then it won't retrieve the first row ACA,ABS,PHR. Can some one please tell me how I can do a text search for codes so as long as one code matches in Col2 I get the row so I can pass in directly the , delimited list and as long as it finds a single match the row is retrieved.

Thanks

Best Answer

You could also split the string and then search on the splitted values. An example could be by creating and using this function.

Starting from SQL Server 2016 you could use the built in STRING_SPLIT() function.

Creating the function from the blogpost and executing this query afterwards

SELECT DISTINCT Col1,Col2 
FROM dbo.Searching
CROSS APPLY dbo.fnSplitString(col2,',')
WHERE splitdata in ('ABS','MCM');

Result

Col1    Col2
1   ACA,ABS,PHR
3   MCM

DB<>Fiddle

An optimization could be proactively splitting the table into a permanent one so you don't need to do splitting on the entire table everytime. (If the values are static)

EDIT

Comment by @Erikdarling

There are better ways to split strings: Comparing string splitting / concatenation methods, including a much better one at SSC by Jeff Moden.

The function by Jeff Moden

After creating the function in his blogpost, you could use a query that resembles the previous one.

  SELECT Col1,Col2
  FROM dbo.Searching
  CROSS APPLY dbo.DelimitedSplit8k(Col2,',') split
  WHERE split.Item in ('ABS','MCM');

DB<>Fiddle

Related Question