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

User · Answer

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

User · Answer

The solution tbaxter120 suggested worked for me but I needed something that will be supported both in MySQL  amp  Oracle  amp  MSSQL  and here it is  WHERE  CONCAT      CONCAT RTRIM MyColumn          LIKE CONCAT        CONCAT  search

User · Answer

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

User · Answer

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

User · Answer

select   from YourTable where     replace col               like    Cat

User · Answer

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

User · Answer

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

User · Answer

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  gt 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

User · Answer

SELECT   FROM TABLE NAME WHERE                       LOCATE   DOG    CONCAT     COLUMN       gt 0 OR             LOCATE   CAT    CONCAT     COLUMN       gt 0

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Where value in column containing comma delimited values

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2008

Examples related to csv