Where value in column containing comma delimited values

Question

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

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?

Use a separate table with one row per entry not comma delimited multi valued lists. — Martin Smith, Apr 10 '11 at 12:29
Would love to, and I would do it this way if I had a choice but unfortunately I don't. I'm using an existing database - this is what I have to work with... — Neaox, Apr 10 '11 at 12:35

score 121 · Accepted Answer · answered Dec 11 '13 at 17:27

121

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

answered Dec 11 '13 at 17:27

tbaxter120

1,371
1
11
8

1

I know this question was asked a long time ago but good catch and nice way to minimise the checks. I have now marked yours as the correct answer. – Neaox Dec 11 '13 at 20:23
Doesn't this add the empty string to the items if the List ended with a `,`? For example `Cat, Dog, Sparrow, Trout, Cow, Seahorse,` will match `@search=""` – ytoledano Jan 24 '16 at 11:42
1

Thank you, this is brilliant! – Blindy Oct 27 '16 at 14:47
This is so interesting and deserve point, but I think about finding if one of item in first list exist in second list. – QMaster Mar 09 '18 at 21:02
1

I know it's an old post but I still found it helpful. Simple solution through a wee bit of string manipulation, I like it. Now if the former db designers knew how to normalize tables properly, I wouldn't have had to use this in the first place. – Phaelax z Dec 02 '19 at 20:16
such a simple solution!! thanx – Insano Apr 20 '20 at 10:26
Great solution! For people reading this. Don't forget that in some SQL dialects concatenating a string/text is done by using the `||` operator instead of the `+` – Jesse de gans Oct 19 '21 at 15:02

gbn · Answer 2 · 2011-04-11T08:42:01.210

29

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)

edited Apr 11 '11 at 08:42

answered Apr 10 '11 at 19:49

gbn

422,506
82
585
676

3

I believe you'd also need a 4th case for when `MyColumn` only has one element: `... OR MyColumn = @search` – Cheran Shunmugavel Apr 11 '11 at 01:05
1

I had a similar issue except I was not getting enough returns, running that query in Base showed only a few of the matches. I changed it to just " MyColumn LIKE '%' + @search + '%' " and it worked a treat – JulianB Dec 11 '12 at 15:44

score 11 · Answer 3 · answered Sep 01 '14 at 13:53

11

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

answered Sep 01 '14 at 13:53

Liquinaut

3,759
1
21
17

4

The question was about SQL Server 2008 which doesn't have that built-in function. – Robert Gabriel Aug 02 '17 at 02:31

score 5 · Answer 4 · answered Apr 10 '11 at 12:40

5

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.

answered Apr 10 '11 at 12:40

Martin Smith

438,706
87
741
845

That would work but only if the order of the elements in '@search' is exactly the same as in the database. The search 'Dog,Cat' would not bring any results. – Edwin Dalorzo Apr 10 '11 at 12:43
@edalorzo - The OP is only searching for one element at a time according to their question. – Martin Smith Apr 10 '11 at 12:43

score 4 · Answer 5 · edited May 23 '17 at 12:34

4

I found this answer on another forum, works perfect. No problems with finding 1 if there is also a 10

WHERE tablename REGEXP "(^|,)@search(,|$)"

I found it here

edited May 23 '17 at 12:34

Community

1
1

answered Jan 27 '15 at 16:07

Maurits Weebers

69
1
3

score 4 · Answer 6 · answered Apr 10 '11 at 12:45

4

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

answered Apr 10 '11 at 12:45

Mikael Eriksson

136,425
22
210
281

score 3 · Answer 7 · answered Apr 10 '11 at 13:10

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'

score 2 · Answer 8 · edited Apr 25 '12 at 13:33

2

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

edited Apr 25 '12 at 13:33

antyrat

27,479
9
75
76

answered Apr 25 '12 at 12:51

D3MO

29
1

Brilliant and perfect solution. Thanks Man. – RK12 Aug 07 '19 at 07:25

score 1 · Answer 9 · edited Oct 27 '12 at 22:48

1

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

edited Oct 27 '12 at 22:48

keyser

18,829
16
59
101

answered Feb 03 '12 at 15:20

S Ravi Kumar

11
1

This is for SQL Server only? As far as I know MySQL does not provide this for example (just trying to clarify for others like me who found this answer even though we aren't using SQLServer) – dominicbri7 Jul 10 '13 at 14:54

score 0 · Answer 10 · edited Dec 22 '16 at 20:31

0

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.

edited Dec 22 '16 at 20:31

Petter Friberg

21,252
9
60
109

answered Dec 22 '16 at 11:39

stu

196
1
7

score 0 · Answer 11 · answered Mar 09 '18 at 20:57

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.

score 0 · Answer 12 · answered Apr 25 '18 at 12:59

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)

score 0 · Answer 13 · answered Apr 10 '11 at 12:38

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.

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.

score 0 · Answer 14 · answered Aug 13 '20 at 13:56

0

The solution tbaxter120 suggested worked for me but I needed something that will be supported both in MySQL & Oracle & MSSQL, and here it is:

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

answered Aug 13 '20 at 13:56

Ziv Foox

376
3
6

score 0 · Answer 15 · answered Dec 21 '21 at 18:10

0

Only if SQL Server 2016 or later - Using STRING_SPLIT

SELECT Column From Table 
WHERE EXISTS (SELECT *  FROM STRING_SPLIT(Column , ',') WHERE value IN ('Cat'));

answered Dec 21 '21 at 18:10

Sudhakar B

1,465
9
16

Where value in column containing comma delimited values

15 Answers15

Linked

Related