mysql query with regexp

Question

I have a MySQL table like this

restaurant_id   restaurant_name     restaurant_serving_cuisines
1               Vasantha Bhavan     4,5,6
3               Little India        7,5,6
5               Mohan Restaurants   16,2,4,1,5,3,6
6               Sri Restaurant      34,16,21,2,23,38,30,7,25,9
13              The Taco Shop       5
22              KFC                 15
37              belargio            14,15,16,2,7,4,1,5,17,12,3,13,6
56              Pizza Hot           5

I need to get the restaurant which is serving the cuisine id with 5 & 15. I need to get the restaurant as belargio.

I am writing the query

SELECT restaurant_id, restaurant_name,restaurant_serving_cuisines 
FROM `rt_restaurant`
WHERE restaurant_serving_cuisines REGEXP concat_ws("|",    "5",    "15");

But I cant get the exact result.

If you have columns containing comma-separated lists of something you should check your data-model. restaurant_serving_cuisines should go to a separate table and there would be no need for regex at all. — piet.t
– piet.t, Commented Nov 27, 2013 at 13:58
regexp is probably overkill. Look into the LIKE function or FIND_IN_SET. (Besides being overkill, your example has the comma separated values NOT in a specified order. You would at least need to guarantee an order for regexp to work) — AgRizzo
– AgRizzo, Commented Nov 27, 2013 at 14:04
Hello, I strongly recommend that you NORMALIZE restaurant_serving_cuisines column. Normalize can help you when you do COUNT, MAX, MIN, SORT, inserting new cuisines, update, delete and so on... — Jason Heo
– Jason Heo, Commented Nov 27, 2013 at 14:05

juergen d · Accepted Answer · 2013-11-27 14:01:25Z

1

How about using FIND_IN_SET

SELECT restaurant_id, restaurant_name,restaurant_serving_cuisines 
FROM `rt_restaurant` 
WHERE find_in_set(5, restaurant_serving_cuisines) > 0
and find_in_set(15, restaurant_serving_cuisines) > 0

answered Nov 27, 2013 at 14:01

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Teejay · Accepted Answer · 2013-11-27 14:15:58Z

0

FIND_IN_SET is good but too specific.

This should work for any DBMS:

SELECT restaurant_id, restaurant_name, restaurant_serving_cuisines 
FROM `rt_restaurant` 
WHERE ',' + restaurant_serving_cuisines + ',' LIKE '%,5,%'
  AND ',' + restaurant_serving_cuisines + ',' LIKE '%,15,%'

Anyway, you should probably check your data model, as already suggested:
restaurant_serving_cuisines should go to a separate table

edited Nov 27, 2013 at 14:15

answered Nov 27, 2013 at 14:09

Teejay

7,55111 gold badges52 silver badges80 bronze badges

Collectives™ on Stack Overflow

mysql query with regexp

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related