MySQL Regexp for Finding a CSV number

Question

How do you find a number (e.g., "8") in a CSV string in a MySQL field without finding numbers that contain that number (e.g., "18" or "81")?

For example:

I need to have #1 and #2 come up true and #3 come up false. How do I do that

SELECT * FROM sometable WHERE somecolumn REGEXP '(what here?)';

ajreal · Accepted Answer · 2011-01-14 10:15:15Z

3

use find_in_set

select find_in_set('8', '18,81,82,88');
--> zero

select find_in_set('8', '8,14,18');
--> 1

select find_in_set('8', '4,5,8,13');
--> 3

So,

select * from your_table
where find_in_set('8', your_col)<>0;

PS: normalize your data to avoid future problem

answered Jan 14, 2011 at 10:15

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

same as not equal (not smaller than, and not greater than)