包含逗号分隔值的列中的值在哪里

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:

你好,我想知道如何编写SQL语句的SQL Server 2008选择条目的一个列包含一个值,现在列中的值是一个逗号分隔的列表(通常只能有一个条目(和没有逗号))检查是什么”这个值包含列表中的某个地方吗?”,比如:

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

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?

我也不能使用包含()或“喜欢”,在上面的示例中为“马”将返回值作为整个字符串包含马‘海马’,而且我不能寻找针加一个逗号(如果我在找“马”的搜索将“马”),如果条目的列表吗?我不能搜索一个逗号加一个针(如果我在寻找“horse”，那么搜索将会是“horse”，如果条目是列表中的第一个条目呢?如果条目是唯一的条目，我不能同时使用它们。

Thanks for any help anyone can give.

谢谢大家的帮助。

Cheers.

欢呼。

13 个解决方案

#1

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 + ',%'

#2

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)

#3

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

#4

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.

当然，这需要对每次搜索进行全表扫描。

#5

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.html or using plain SQL.

为此，您可以使用CLR http://bi-tch.blogspot.com/2007/10/sql-clr-net-function-split.html或使用纯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'

#6

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(,|$)"

I found it here

我发现在这里

#7

select *
from YourTable
where ','+replace(col, ' ', '')+',' like '%,Cat,%'

#8

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

#9

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开始。

#10

SELECT * FROM TABLE_NAME WHERE
        (
            LOCATE(',DOG,', CONCAT(',',COLUMN,','))>0 OR
            LOCATE(',CAT,', CONCAT(',',COLUMN,','))>0
        );

#11

If you know the ID's rather than the strings, use this approach:

如果你知道ID而不是字符串，使用以下方法:

where mylookuptablecolumn IN (myarrayorcommadelimitedarray)

Just make sure that myarrayorcommadelimitedarray is not put in string quotes.

只需确保myarrayorcommadelimitedarray没有放在字符串引号中。

works if you want A OR B, but not AND.

如果你想要A或B，可以用，但不能用AND。

#12

Although the tricky solution @tbaxter120 advised is good but I use this function and work like a charm, pString is a delimited string and pDelimiter is a delimiter character:

虽然建议使用棘手的解决方案@tbaxter120很好，但是我使用这个函数并像咒语一样工作，pString是带分隔符的字符串，pDelimiter是带分隔符的字符:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[DelimitedSplit]
--===== Define I/O parameters
        (@pString NVARCHAR(MAX), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL -- does away with 0 base CTE, and the OR condition in one go!
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ---ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,50000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l

;

Then for example you can call it in where clause as below:

例如，你可以在下面的where子句中调用它:

WHERE [fieldname] IN (SELECT LTRIM(RTRIM(Item)) FROM [dbo].[DelimitedSplit]('2,5,11', ','))

Hope this help.

希望这个有帮助。

#13

Where value in column containing comma delimited values search with multiple comma delimited

在包含逗号分隔值的列中，用多个逗号分隔值搜索的值在哪里

            declare @d varchar(1000)='-11,-12,10,121'

            set @d=replace(@d,',',',%'' or '',''+a+'','' like ''%,')

            print @d
            declare @d1 varchar(5000)=
            'select * from (
            select ''1,21,13,12'' as a
            union
            select ''11,211,131,121''
            union
            select ''411,211,131,1211'') as t
             where '',''+a+'','' like ''%,'+@d+ ',%'''

             print @d1
             exec (@d1)

#1