其中，在包含逗号分隔值的列中的值 [英] Where value in column containing comma delimited values

问题描述

我想知道如何为SQL Server 2008写一个SQL语句选择条目的列包含一个值，现在列中的值是一个逗号分隔列表（通常 - 只能有一个条目（和没有例如：

  COLUMN =猫，狗，麻雀，鳟鱼，牛，海马
 COLUMN是否含有猫？ YES 
 COLUMN是否包含马？ NO 
 COLUMN是否包含羊？ NO 
  

或

 code> COLUMN = Mouse 
 COLUMN是否包含Hare？ NO 
 COLUMN是否包含鼠标？ YES 
  

等

可以使用'IN'关键字，因为

  SELECT id_column FROM table_name WHERE'Cat'IN COLUMN 
  

但这不工作，因为它似乎只能使用它来检查列是否包含一系列逗号分隔值。

我也不能使用CONTAINS（）OR'LIKE'，因为上面的例子会返回'horse'的值，因为整个字符串包含马在'Seahorse '，我不能搜索针加一个逗号（如果我正在寻找'马'搜索将是'马'，如果该条目是在列表的结尾如果）。我不能搜索逗号加针（如果我正在寻找'马'搜索将是'，马'）
如果该条目是列表中的第一个如果怎么办？

感谢任何人可以给予任何帮助。

干杯。

解决方案

如果我在列表'17，34,400,12'中寻找'40'，那么它将找到，40并返回不正确的条目。这将照顾所有解决方案：

  WHERE（'，'+ RTRIM（MyColumn）+'，'）LIKE' '+ @search +'，％'
  

Hi I am wondering how to write an SQL statement for SQL Server 2008 that Selects entry's where a column contains a value, now the value within the column is a comma delimited list (usually - there could only be one entry (and no leading comma)) so what In checking for is "is this value contained somewhere within the list?", for instance:

COLUMN = Cat, Dog, Sparrow, Trout, Cow, Seahorse
Does COLUMN contain Cat? YES
Does COLUMN contain horse? NO
Does COLUMN contain Sheep? NO

or

COLUMN = Mouse
Does COLUMN contain Hare? NO
Does COLUMN contain Mouse? YES

etc

I was thinking I could use the 'IN' keyword as such

SELECT id_column FROM table_name WHERE 'Cat' IN COLUMN

but this does not work as it seems that you can only use that to check if a column contains one of a series of comma delimited values.

I also cannot use CONTAINS() OR 'LIKE' as this, in the above example would return values for 'horse' as the whole string contains horse in 'Seahorse', and I can't search for the needle plus a comma (if I'm looking for 'horse' the search would be 'horse,') as what if the entry is at the end of a the list? And I can't search for a comma plus a needle (if I'm looking for 'horse' the search would be ',horse') as what if the entry is the first in the list? And I can't use both as what if the entry is the only (single) entry?

Thanks for any help anyone can give.

Cheers.

解决方案

There is one tricky scenario. If I am looking for '40' in the list '17,34,400,12' then it would find ",40" and return that incorrect entry. This takes care of all solutions:

WHERE (',' + RTRIM(MyColumn) + ',') LIKE '%,' + @search + ',%'

这篇关于其中，在包含逗号分隔值的列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！