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 corectly parse such strings: '5,7'; '3,5,7'; '3,5'; '5' and should block such string: '3,55,7';

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

Leave a Reply

Your email address will not be published. Required fields are marked *