MySQL How LIKE 1 digit-CodePudding

I have this records:

Number
1, 2
1, 24

How to select only 2 by LIKE not 2 and 24

SELECT * FROM test WHERE number LIKE '%2%'

1, 2 // need only this record

1, 24

CodePudding user response：

You should avoiding storing unnormalized CSV data like this. That being said, if you must proceed, here is one way:

SELECT * 
FROM test
WHERE CONCAT(' ', number, ' ') LIKE '% 2 %';

CodePudding user response：

find_in_set almost does what you want, but you'll have to remove the spaces in order to use it:

SELECT * 
FROM   test
WHERE  FIND_IN_SET('2', REPLACE(number, ' ', '')) > 0

CodePudding user response：

You can do it as follows :

SELECT `number`
FROM `test` 
WHERE TRIM(SUBSTRING_INDEX(`number`, ',', -1)) = 2 or TRIM(SUBSTRING_INDEX(`number`, ',', 1)) = 2;

SUBSTRING_INDEX to split number, and TRIM to remove any space, then we search in to parts of the number.