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

问题描述

我希望为 SQL Server 2008 编写一个 SQL 语句，该语句选择列包含值的条目，现在列中的值是一个逗号分隔的列表(通常 - 只能有一个条目(并且没有前导逗号)) 那么 In 检查的是这个值是否包含在列表中的某个地方?"，例如:

I wish 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

或

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

等

我想我可以像这样使用IN"关键字

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.

我也不能使用 CONTAINS() 或 'LIKE' 作为这个，在上面的例子中将返回 'horse' 的值，因为整个字符串包含 'Seahorse' 中的 horse，并且我无法搜索针加上一个逗号(如果我正在寻找 'horse'，则搜索将是 'horse')，如果条目位于列表的末尾会怎样?而且我无法搜索逗号加针(如果我正在寻找horse"，则搜索将是,horse")如果条目是列表中的第一个呢?如果条目是唯一的(单个)条目，我不能同时使用两者?

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?

推荐答案

有一个棘手的场景.如果我在列表 '17,34,400,12' 中查找 '40' 那么它会找到 ",40" 并返回那个不正确的条目.这会处理所有解决方案:

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屋！