Filter by using in for SQL Server 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
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&lt;>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&lt;>Fiddle