Multiple LIKE in SQL

Question

I wanted to search through multiple rows and obtain the row that contains a particular item.

The table in mySQL is setup so each id has a unique list (comma-delimited) of values per row.

Ex:

id | order
1  | 1,3,8,19,34,2,38
2  | 4,7,2,190,38

Now if I wanted to pull the row that contained just the number 19 how would I go about doing this? The possibilities I could figure in the list with a LIKE condition would be:

19,     <-- 19 at the start of the list
,19     <-- 19 at the end of the list
,19,    <-- 19 inside the list

I tried the following and I cannot obtain any results, Thank you for your help!

SELECT *
FROM categories
WHERE order LIKE '19,%' OR '%,19%' OR '%,19%'
LIMIT 0 , 30

Answer 1

First of all, you should not store values like this, you should use a child table with one row per item, this would make your current query that much easier to handle, and would allow for indexes to be used as well.

Having said that, what you want is this:

WHERE ',' + order + ',' LIKE '%,19,%'

By adding a comma in front of and after the list of values in the order field, you can query that field for the value you want with a comma on each side, and not have to deal with the special case of the value being the first or last value in the list.

With the query you listed, you could've made it work by realizing that OR doesn't give more arguments to the LIKE clause, it separates entirely different clauses, and thus rewritten the SQL like this:

WHERE order LIKE '19,%' OR order LIKE '%,19,%' OR order LIKE '%,19'
                           ---+------      ^     ----+-----       ^
                              |            |         |            |
                              +- add this -+---------+            +- removed
                                                                     percent

Note the missing comma in the second pattern there, which would match strings containing the value 19, and note the removed percentage character in the last ,which would allow it to match lists that ended with 19.

Answer 2

You can use FIND_IN_SET to solve your problem in a simpler way:

SELECT *
FROM categories
WHERE FIND_IN_SET('19', `order`)
LIMIT 0, 30

In general though you should try to design your database so that this type of operation is not necessary.