Where Value in Column Containing Comma Delimited Values

Where value in column containing comma delimited values

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

Select a Value from a table which contains Comma separated values in SQL Server

You can use PATINDEX() function
The PATINDEX() function returns the position of a pattern in a string.

If the pattern is not found, this function returns 0.

SELECT *, X.Order
FROM Table1 X
JOIN Table2 Y
    ON X.ID = Y.ID
WHERE
    X. Person = 'Person3' AND
    PATINDEX ('%,Res7,%',CONCAT(',',Y.Resource,',') )>0
  

Querying a column that contains comma separated values

Thanks to @Obie and @AllanS.Hansen I knew where to start looking to fix this. Its pretty rough, but I wanted to post a solution before I got too far down the rabbit hole:

DECLARE variable1 varchar(max) = '' --around 9 passed from code

DECLARE @query nvarchar(max);
DECLARE @column_list varchar(max) = 'column1, column2, etc'
--one of each of the tests per variable passed from code
DECLARE @variable1_test nvarchar(max) = (SELECT CASE WHEN @variable = '' THEN '%' ELSE (SELECT * from dbo.stored_function(@variable, 'column_name')) END);
END;

SET @query = ' SELECT ' + @column_list + ' 
        FROM table_name 
        WHERE variable LIKE ''' + @variable_test + ''' '
EXECUTE sp_executesql @query;
print(@query); --This is just to see the resulting query, which helped me a ton

Exciting! Now I have to test it.

How to run select query on columns having comma separated values

Use the operator LIKE:

SELECT USER 
FROM DETAILS 
WHERE ',' || DEPARTMENT || ',' LIKE '%,' || 'Admin' || ',%'
  AND ',' || DEPARTMENT || ',' LIKE '%,' || 'Finance' || ',%'
  AND ',' || DEPARTMENT || ',' NOT LIKE '%,' || 'Accounts' || ',%';

Or the function INSTR():

SELECT USER 
FROM DETAILS 
WHERE INSTR(',' || DEPARTMENT || ',', ',' || 'Admin' || ',') > 0
  AND INSTR(',' || DEPARTMENT || ',', ',' || 'Finance' || ',') > 0
  AND INSTR(',' || DEPARTMENT || ',', ',' || 'Accounts' || ',') = 0;

This will work if there are no spaces after each comma in the column DEPARTMENT.

See the demo.

How can I check whether a number is contained in comma separated list stored in a varchar column?

You should really redesign this table to split out those values from being comma separated to being in individual rows. However, if this is not possible, you are stuck with doing string comparison instead:

DECLARE @id INT = 3
DECLARE @stringId VARCHAR(50) = CAST(@id AS VARCHAR(50))

SELECT * 
FROM MyTable 
WHERE categoryIds = @stringId -- When there is only 1 id in the table
OR categoryIds LIKE @stringId + ',%' -- When the id is the first one
OR categoryIds LIKE '%,' + @stringId + ',%' -- When the id is in the middle
OR categoryIds LIKE '%,' + @stringId -- When the id is at the end

Check if comma delimited column contains a value

You can use boundaries '\b' to look for the number. This will ensure things like 1475 24756 are not matched

data$has_475 <- grepl('\\b475\\b', data$b)
data
  a            b has_475
1 1          123   FALSE
2 2     6475,320   FALSE
3 3          475    TRUE
4 4 905,1204,543   FALSE
5 5      567,475    TRUE
6 6         1475   FALSE

Searching a cell with a string of comma delimited values in Excel

Use FILTERXML:

=IF(OR(ISNUMBER(SEARCH(FILTERXML(""&substitute(INDEX(CODES, MATCH([@A], CODES[A], 0), MATCH("B", CODES[#Headers],0)),",","")&"","//b"),[@B]))), "NOK", "OK")

Make sure you change #Headers to your language if different than English

This will need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode in Excel 2013.