Compare comma delimited strings in SQL

问题:

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?

回答1:

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

回答2:

You can convert the lists to a table with the following function:

CREATE FUNCTION DelimitedStringToTable (@cdl varchar(8000), @delimiter varchar(1))
RETURNS @list table (
    Token varchar(1000)
)
AS
BEGIN

DECLARE @token varchar(1000)
DECLARE @position int

SET @cdl = @cdl + @delimiter -- tack on delimiter to end 
SET @position = CHARINDEX(@delimiter, @cdl, 1)
    WHILE @position > 0
        BEGIN
            SET @token = LEFT(@cdl, @position - 1)
            INSERT INTO @list (Token) VALUES (@token)
            SET @cdl = RIGHT(@cdl, DATALENGTH(@cdl) - @position)        
            SET @position = CHARINDEX(@delimiter, @cdl, 1)      
        END 
    RETURN
END

Then you can use something like this to find all the matches:

SELECT list1.* 
FROM DelimitedStringToTable('1,2,3', ',') list1
INNER JOIN DelimitedStringToTable('2', ',') list2 ON list1.Token = list2.Token