Compare comma delimited strings in SQL

database, sql, sql-server-2008, t-sql, varchar

Solution

Just one step further, get closer to your answer. SQL FIDDLE DEMO

SELECT P.* 
FROM Product P
CROSS APPLY(
  SELECT *
  FROM ShelfSearch SS
  WHERE Patindex(char(37)+replace(ss.shelflist, ',',char(37))+char(37),p.shelflist) > 0
)Shelfs

Problem

I have search requests that come in a CDL `("1,2,3,4")`,`("1,5")`. I need to compare that to another CDL and return back all records that have a match. The kicker is the position of each number isn't always the same. I've got something almost working except for instances where I'm trying to match `("2,5")` to `("2,4,5")`. Obviously the strings aren't equal but I need to return that match, because it has all the values in the first CDL. My SQL Fiddle should make it fairly clear... Any help would be much appreciated. Oh and I saw this one is similar, but that seems a little drastic and over my head but I'll see if I can try to understand it. Edit So I just did a replace to change `("2,5")` to `("%2%5%")` and changed the were to use `LIKE`. From what I can initially tell it seems to be working.. SQL Fiddle Any reason I shouldn't do this or maybe I'm crazy and it doesn't work at all?

Original source