比较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.

我有搜索请求，以CDL（“1,2,3,4”），（“1,5”）。我需要将它与另一个CDL进行比较并返回所有匹配的记录。踢球者是每个数字的位置并不总是相同的。

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.

除了我试图匹配（“2,5”）到（“2,4,5”）的情况之外，我有一些几乎可以工作的东西。显然字符串不相等但我需要返回该匹配，因为它具有第一个CDL中的所有值。

My SQL Fiddle should make it fairly clear...

我的SQL小提琴应该让它相当清楚......

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?

编辑所以我只是做了一个替换以更改（“2,5”）到（“％2％5％”）并更改了使用LIKE。从我最初可以说它似乎工作.. SQL小提琴任何理由我不应该这样做或者也许我疯了，它根本不起作用？

2 个解决方案

#1

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

更进一步，更接近你的答案。 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

#1