finding a number in space separated list with REGEXP

Question

I am writing a SQL query to select row, where a field with space separated numbers contains a single number, in this example the 1.

Example fields:

• "1 2 3 4 5 11 12 21" - match, contains number one
• "2 3 4 6 11 101" - no match, does not contain number one

The best query so far is:

$sql = "SELECT * from " . $table . " WHERE brands REGEXP '[/^1$/]' ORDER BY name ASC;";

Problem is that this REGEXP also finds 11 a match

I read many suggestions on other post, for instance [\d]{1}, but the result always is the same. Is it possible to accomplish what I want, and how?

Answer 1

You don't need regex: You can use LIKE if you add a space to the front and back of the column:

SELECT * from $table
WHERE CONCAT(' ', brands, ' ') LIKE '% 1 %'
ORDER BY name

Answer 2

Try:

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

[[:<:]] and [[:>:]] match word boundaries before and after a word.