0

I have a table, one of the columns contains a text values, some of which are comma separated string, like this:

Downtown, Market District, Warehouse District

I need to modify my query to see is a given value matches this column. I decided that using IN() is the best choice.

SELECT * 
FROM t1
WHERE myValue IN (t1.nighborhood)

I am getting spotty results - sometimes I return records and sometimes not. If there's a value in t1.nighborhood that matches myValue, I do get data.

I checked and there are no MySQL errors. What am I missing?

santa
  • 12,234
  • 49
  • 155
  • 255
  • *If there's a value in t1.nighborhood that matches myValue, I do get data.* Isn't that what you want? When doesn't it work? – shmosel Jan 10 '17 at 22:22
  • It doesn't work when the myvalue = Downtown and t1.nighborhood = Downtown, Market District, Warehouse District. However it DOES work when t1.nighborhood = Downtown – santa Jan 10 '17 at 22:24
  • That makes sense. See my answer. – shmosel Jan 10 '17 at 22:24
  • 1
    See my answer to [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Jan 10 '17 at 22:33

1 Answers1

0

You can use FIND_IN_SET() to search a comma-delimited list:

SELECT * 
FROM t1
WHERE FIND_IN_SET(myValue, REPLACE(t1.nighborhood, ', ', ','));

The REPLACE() is necessary to remove the extra spaces.

Another solution is to use regex to match your search value surrounded by commas if necessary:

SELECT * 
FROM t1
WHERE t1.nighborhood REGEXP CONCAT('(^|, )', myValue, '(, |$)');

In general, it's bad design to store distinct values in a single column. The data should be normalized into a related table with a foreign key.

shmosel
  • 49,289
  • 6
  • 73
  • 138
  • Unfortunately still the same outcome. Here's my query: SELECT gb_restaurants.*, business_profile.busID, business_profile.busOnline, gb_restaurant_bounty.* FROM gb_restaurants LEFT JOIN business_profile ON (business_profile.yelpAPIid = gb_restaurants.yelpAPIid) LEFT JOIN gb_restaurant_bounty ON (gb_restaurant_bounty.bounty_busID = gb_restaurants.id) WHERE gb_restaurants.busState = 'TX' AND restaurants.busCity = 'Austin' AND FIND_IN_SET('Arboretum', REPLACE('restaurants.neighborhood', ', ', ',')) ORDER BY business_profile.busOnline DESC, business_profile.busID DESC – santa Jan 10 '17 at 22:30
  • 1
    @santa `restaurants.neighborhood` is a column name; it shouldn't be in quotes. – shmosel Jan 10 '17 at 22:32
  • That was it! Thanks. – santa Jan 10 '17 at 22:38