Finding a number in the white space with REGEXP

Question

I am writing an SQL query to select a row where a field with numbers separated by spaces contains one number, in this example 1.

Examples of 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

Best request:

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

The problem is that this one REGEXPalso finds 11 a match

I read a lot of sentences on another post, for example [\d]{1}, but the result is always the same. Can I do what I want and how?

Answer 1

Try:

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

[[:<:]]and [[:>:]]correspond to the word boundaries before and after the word.

Answer 2

: LIKE, :

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

Answer 3

Why not FIND_IN_SET()+REPLACE()

SELECT 
    *
FROM
    `table`
WHERE
    FIND_IN_SET(1, REPLACE(`brands`, ' ', ','))
ORDER BY
    `name` ASC;