MySQL check if value is in comma separated string

MySQL check if value is in comma separated string:
For example, we have field 'category' with comma separated string of categories (3,5,7) and we want to check if our value is in this string.

The next code does not work:

select * from tablename
where {$cat_id} in (tablename.category)
order by tablename.date desc;

So we have to use regular expression:

select * from tablename
where tablename.category REGEXP '^{$cat_id}[,]|[,]{$cat_id}[,]|[,]{$cat_id}$|^{$cat_id}$'
order by tablename.date desc;

For example, we have id of our category="5". Our regular expression should correctly parse such strings: '5,7'; '3,5,7'; '3,5'; '5' and should block such string: '3,55,7';

1 thought on “MySQL check if value is in comma separated string”

Leave a Comment