比较SQL中逗号分隔的字符串 [英] Compare comma delimited strings in SQL

问题描述

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

我有一些几乎工作除了我试图匹配的例子（2,5）到（2,4,5）。显然字符串不相等，但我需要返回匹配，因为它具有第一个CDL中的所有值。

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

哦，我看到这个一个是相似的，但是这似乎有点激烈，在我的头，但我会看看，如果我可以尝试理解它。

编辑

所以我只是更换了 ）到（％2％5％）并更改了 LIKE 。从我最初可以告诉它似乎正在工作.. SQL Fiddle

解决方案

只要一个，我就不会这么做，进一步，更接近你的答案。

SQL FIDDLE DEMO

  
 SELECT P. * 
 FROM产品P 
交叉申请（
 SELECT * 
 FROM ShelfSearch SS 
 WHERE Patindex（char（37）+ replace（ss.shelflist，'，'，char（37））+ char（37），p.shelflist）> 0 
）Shelfs 
  

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?

解决方案

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

这篇关于比较SQL中逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！