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.

I'm sorry you're stuck with this schema. – Joe Dec 09 '11 at 00:08 — Joe, Dec 09 '11 at 00:08
Wow, agree with @Joe and others. Terrible schema. – davidethell Dec 09 '11 at 00:18 — davidethell, Dec 09 '11 at 00:18

score 7 · Accepted Answer · edited May 23 '17 at 12:24

7

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?

edited May 23 '17 at 12:24

Community

1
1

answered Dec 09 '11 at 00:16

Bill Karwin

538,548
86
673
828

+1 thanks for the into to the Find_In_Set function. Sadly, I'm going to find that useful. – dash Dec 09 '11 at 00:20
This answer summarizes why Bill has 94k points and I have only 763 points :D +1 for the nice answer, I totally forgot about regex and FIND_IN_SET – Savas Vedova Dec 09 '11 at 00:24
Nice find, Bill. To the OP -- long as you stay in MySQL, this is a good solution. :) – Joe Dec 09 '11 at 01:11

score 4 · Answer 2 · answered Dec 09 '11 at 00:11

4

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,%';

answered Dec 09 '11 at 00:11

Savas Vedova

5,622
2
28
44

2

+1 for covering bases. Lets hope there aren't any spaces though :-) – dash Dec 09 '11 at 00:17

Joe · Answer 3 · 2011-12-09T01:10:45.123

3

For this specific case, you could do something like:

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

assuming there's no whitespace between numbers and commas.

But that's ugly, not to mention will be unable to use any kind of indexing at all.

edited Dec 09 '11 at 01:10

answered Dec 09 '11 at 00:10

Joe

41,484
20
104
125

score 2 · Answer 4 · answered Dec 09 '11 at 00:25

2

You should read about table normalization. Here's a good introduction.

answered Dec 09 '11 at 00:25

Ignas

1,965
2
17
44

SQL - Where like statement

4 Answers4