Rei Rei - 6 months ago 11
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

This way you can achieve what you want:

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

SQL FIDDLE DEMO

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.

Suggestion:

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

Comments