CODEWITHSUNDEEP
phpmysqlsqldatabase
Flexer
Flexer

Reputation: 131

SQL - Where like statement

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.

Upvotes: 4

Views: 217

Answers (4)

Joe
Joe

Reputation: 42666

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.

Upvotes: 3

Ignas
Ignas

Reputation: 1965

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

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 563021

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?

Upvotes: 7

Savas Vedova
Savas Vedova

Reputation: 5692

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

Upvotes: 4

Related Questions