SQL - Where like statement

Question

I have a table with two columns (id, numberslist).

How can I get all rows that have exactly "1" in numberslist (in this case id = 2)

Whats the sql statement?

---

id | numberslist

1 | 11,111
2 | 1,2,3

---

This doesnt work:

$sql = "SELECT * FROM mytabelle WHERE numberslist LIKE '%1%'";

I know it's a bad database design to list all numbers in this way but its not changeable.

Answer 1

MySQL supports "word boundary" patterns in its regular expression syntax for this purpose.

WHERE numberlist REGEXP '[[:<:]]1[[:>:]]'

You could alternatively use FIND_IN_SET():

WHERE FIND_IN_SET('1', numberlist) > 0

That said, I agree with comments in other answers that storing a comma-separated list in a string like this is not good database design. See my answer to Is storing a comma separated list in a database column really that bad?

Answer 2

The design is really bad. Anyways, for this bad design, this bad code should do the work :D

SELECT * FROM mytabelle WHERE numberslist = '1' OR
                              numberslist LIKE '%,1' OR 
                              numberslist LIKE '%,1,%' OR 
                              numberslist LIKE '1,%';