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