Rei Rei - 2 years ago 57
SQL Question

how to select query with where clause data in row separated by comma

honestly i don't know the right title for my problem and i am still newbie about query things. So I have table name EVENT like this

id_event name_event category
1 Market Food,Fashion,Art
2 Concert Music
3 FoodTruck Food,Bevarage

My problem is i want to select category where category = "Food,Fashion" . So All category which have "Food" and "Fashion" will be out and the result be like this

id_event name_event category
1 Market Food,Fashion,Art
3 FoodTruck Food,Bevarage

Maybe someone can help me, Thank You Guys and Have a nice day!! Cheerss!!

Answer Source

This way you can achieve what you want:

FROM event
WHERE category REGEXP CONCAT('(^|,)(', REPLACE("Food,Fashion", ',', '|'), ')(,|$)');


Note: If you want to see the generated regular expression in the above query:

SELECT CONCAT('(^|,)(', REPLACE("Food,Fashion", ',', '|'), ')(,|$)') AS 'REGEXP';

Result: (^|,)(Food|Fashion)(,|$)

Explanation: Records will be selected having Food/fashion in category name only if anyone of the following condition is met:

  • food/fashion has a comma before it or
  • category starts with food/fashion or
  • category ends with food/fashion or

  • food/fashion has a comma after it.


Is storing a delimited list in a database column really that bad?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download