mysqlrlike

Mysql Rlike condition

I have a table name company

Company  |  category_id
NEC      |     cg17,cg19,cg23
Huawei   |     cg55,cg17,cg25

Which I query like this:

select * from company
where category_id RLIKE '^cg17,cg19'

with result:

Company  |  category_id
NEC      |  cg17,cg19,cg23

but when I query like this:

select * from company
where category_id RLIKE '^cg17,cg23'

I get no rows, but I want company "NEC", because NEC has category_id "cg17,cg19,cg23"

How to fix this?

Solution

  • One way, if your categories are always in order and don't include categories with more than 2 digits:

    select * from company
where category_id LIKE 'cg17%cg23'

    A better way IMHO, which treats the field as a proper CSV value:

    select * from company
where FIND_IN_SET('cg17', category_id) > 0
and FIND_IN_SET('cg23', category_id) > 0