SQL 其中包含逗号分隔值的列中的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5611715/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Where value in column containing comma delimited values
提问by Neaox
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:
我希望为 SQL Server 2008 编写一个 SQL 语句，该语句选择列包含值的条目，现在列中的值是一个逗号分隔的列表（通常 - 只能有一个条目（并且没有前导逗号））那又怎样在检查是“这个值是否包含在列表中的某个地方？”，例如：
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
我在想我可以像这样使用“IN”关键字
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?
我也不能使用 CONTAINS() 或 'LIKE' 作为这个，在上面的例子中将返回 'horse' 的值，因为整个字符串包含 'Seahorse' 中的 horse，并且我无法搜索针加逗号（如果我正在寻找 'horse' 搜索将是 'horse,') 如果条目位于列表的末尾会怎样？而且我无法搜索逗号加针（如果我要搜索“horse”，则搜索将是“,horse”），如果条目是列表中的第一个会怎样？如果条目是唯一的（单个）条目，我不能同时使用两者？
回答by tbaxter120
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:
有一种棘手的情况。如果我在列表 '17,34,400,12' 中查找 '40' 那么它会找到 ",40" 并返回那个不正确的条目。这会处理所有解决方案：
WHERE (',' + RTRIM(MyColumn) + ',') LIKE '%,' + @search + ',%'
回答by gbn
WHERE
MyColumn LIKE '%,' + @search + ',%' --middle
OR
MyColumn LIKE @search + ',%' --start
OR
MyColumn LIKE '%,' + @search --end
OR
MyColumn = @search --single (good point by Cheran S in comment)
回答by Liquinaut
SELECT * FROM TABLENAME WHERE FIND_IN_SET(@search, column)
If it turns out your column has whitespaces in between the list items, use
如果事实证明您的列在列表项之间有空格，请使用
SELECT * FROM TABLENAME WHERE FIND_IN_SET(@search, REPLACE(column, ' ', ''))
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
回答by Martin Smith
DECLARE @search VARCHAR(10);
SET @search = 'Cat';
WITH T(C)
AS
(
SELECT 'Cat, Dog, Sparrow, Trout, Cow, Seahorse'
)
SELECT *
FROM T
WHERE ', ' + C + ',' LIKE '%, ' + @search + ',%'
This will of course require a full table scan for every search.
这当然需要对每次搜索进行全表扫描。
回答by Maurits Weebers
I found this answer on another forum, works perfect. No problems with finding 1 if there is also a 10
我在另一个论坛上找到了这个答案，效果很好。如果也有 10，则找到 1 没有问题
WHERE tablename REGEXP "(^|,)@search(,|$)"
回答by Mikael Eriksson
select *
from YourTable
where ','+replace(col, ' ', '')+',' like '%,Cat,%'
回答by pcofre
The best solution in this case is to normalize your table to have the comma separated values in different rows (First normal form 1NF) http://en.wikipedia.org/wiki/First_normal_form
在这种情况下，最好的解决方案是规范化您的表，以在不同行中使用逗号分隔值（第一范式 1NF）http://en.wikipedia.org/wiki/First_normal_form
For that, you can implement a nice Split table valued function in SQL, by using CLR http://bi-tch.blogspot.com/2007/10/sql-clr-net-function-split.htmlor using plain SQL.
为此，您可以使用 CLR http://bi-tch.blogspot.com/2007/10/sql-clr-net-function-split.html或使用普通 SQL在 SQL 中实现一个很好的拆分表值函数。
CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
Then you can query the normalized output by using cross apply
然后您可以使用查询标准化输出 cross apply
select distinct a.id_column
from MyTable a cross apply
dbo.Split(A.MyCol,',') b
where b.Data='Cat'
回答by D3MO
SELECT * FROM TABLE_NAME WHERE
(
LOCATE(',DOG,', CONCAT(',',COLUMN,','))>0 OR
LOCATE(',CAT,', CONCAT(',',COLUMN,','))>0
);
回答by S Ravi Kumar
Just came to know about this when I was searching for a solution to a similar problem. SQL has a new keyword called CONTAINS you can use that. For more details see http://msdn.microsoft.com/en-us/library/ms187787.aspx
当我正在寻找类似问题的解决方案时才知道这一点。SQL 有一个名为 CONTAINS 的新关键字，您可以使用它。有关更多详细信息，请参阅http://msdn.microsoft.com/en-us/library/ms187787.aspx
回答by Edwin Dalorzo
Since you don't know how many comma-delimited entries you can find, you may need to create a function with 'charindex' and 'substring' SQL Server functions. Values, as returned by the function could be used in a 'in' expression.
由于您不知道可以找到多少个以逗号分隔的条目，因此您可能需要创建一个带有“charindex”和“substring”SQL Server 函数的函数。函数返回的值可以用在“in”表达式中。
You function can be recursively invoked or you can create loop, searching for entries until no more entries are present in the string. Every call to the function uses the previous found index as the starting point of the next call. The first call starts at 0.
您可以递归调用函数，也可以创建循环，搜索条目直到字符串中不再存在条目。对该函数的每次调用都使用先前找到的索引作为下一次调用的起点。第一个调用从 0 开始。
