Where value in column containing comma delimited v

Question

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.

Answer 1

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.

Answer 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)

Answer 3

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)

Answer 4

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:

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 [fieldname] IN (SELECT LTRIM(RTRIM(Item)) FROM [dbo].[DelimitedSplit]('2,5,11', ','))

Hope this help.

Answer 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

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.

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

select distinct a.id_column
from   MyTable a cross apply
       dbo.Split(A.MyCol,',') b
where  b.Data='Cat'

Answer 6

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

where mylookuptablecolumn IN (myarrayorcommadelimitedarray)

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

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