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

Question

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

Sorry to say you have a wrong database design. Comma separated data in a field is always a sign of that. Please sort that out before going any further because further down the line it will be more difficult. — e4c5, May 14 '16 at 05:42

score 1 · Accepted Answer · edited May 23 '17 at 12:08

1

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?

edited May 23 '17 at 12:08

Community

1
1

answered May 14 '16 at 05:46

1000111

13,169
2
28
37

Thank You @1000111 for your answer and once again thank you for your link – Rei May 14 '16 at 06:06
Thank you very much sir for your explanation!! – Rei May 14 '16 at 06:07
@Rei Except for the last line, this is a terrible solution – Strawberry May 14 '16 at 07:31
@Strawberry hey thank you for your response, yes i know, because i am using separated comma right? – Rei May 14 '16 at 07:59

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

1 Answers1