MySQL WHERE clause with array contains key

Question

I have a table where one of the columns contain arrays of values as varchar fields (strings).

Example: The column named 'categories' has a string value '1,2,3' in row 1, and a string value '4,5,6' in row 2.

I want to use a WHERE clause that allows me to select the row where the 'categories' field contains the key value 2 (i.e. the first row).

How can this be done with MySQL?

I guess this can be done with some sort of a pattern match workaround, but is there a cleaner way to do it similar to the 'IN array' statement? (maybe something that makes use of STRING_SPLIT)

P.S.: Not asking if it is a good practice. I am asking how to get it done.

you should normalize your database instead. Comma-separated values are hard to work with. — Funk Forty Niner, Oct 11 '18 at 20:45
Do not store numbers in delimited strings. That is not the SQL way. — Gordon Linoff, Oct 11 '18 at 20:47
@Strawberry No, the question was not answered. I did not ask if it was a good practice to use this type of approach. I was asking how to get it done. — Neo_999, Oct 12 '18 at 13:17

score 1 · Accepted Answer · answered Oct 11 '18 at 20:43

Use Find_in_set() function.

SELECT * 
FROM your_table_name 
WHERE FIND_IN_SET('2', categories) > 0

PS: You should really avoid storing comma separated lists in a single field. String operation functions like Find_in_set() are inefficient. You should really look to normalize your database structure instead.

MySQL WHERE clause with array contains key

1 Answers1