SQL - Where is a similar statement

Question

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

How can I get all the lines with " 1 " in the list of numbers (in this case id = 2)

What is sql statement?

---

id | numberslist

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

---

This does not work:

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

I know that it is a poor database design to list all numbers in this way, but cannot be changed.

Answer 1

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

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

Alternatively, you can use FIND_IN_SET () :

 WHERE FIND_IN_SET('1', numberlist) > 0 

However, I agree with the comments in other answers that storing a comma-separated list in a line like this is not a good database design. See My answer to Is storing a list with commas in a database column really that bad?

Answer 2

The design is really bad. In any case, 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,%'; 

Answer 3

In this particular case, you can do something like:

 WHERE numberslist = '1' OR numberslist LIKE '1,%' OR numberslist LIKE '%,1,%' OR numberslist LIKE %,1'"; 

if there are no spaces between commas and commas.

But this ugly, not to mention, will not be able to use any indexing at all.

Answer 4

You should read about table normalization. Here is a good introduction .