CODEWITHSUNDEEP
mysql
Rei
Rei

Reputation: 143

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!!

Upvotes: 0

Views: 53

Answers (1)

1000111
1000111

Reputation: 13519

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?

Upvotes: 1

Related Questions