CODEWITHSUNDEEP
mysqlregex
mikejohnvino
mikejohnvino

Reputation: 93

mysql query with regexp

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.

Upvotes: 1

Views: 87

Answers (2)

Teejay
Teejay

Reputation: 7501

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

Upvotes: 0

juergen d
juergen d

Reputation: 204904

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

Upvotes: 1

Related Questions