Welcome Guest, Not a member yet? Register   Sign In
A MySQL query question

Hello and happy new year! I have a field in my MySQL dbase table that collects comma separated data. When I want to retrieve a record based on whether or not a data point is included in that field, I do a simple query like this:

SELECT * FROM table WHERE csv_field LIKE '%women%'

I have to place the % sign on each side of my LIKE clause because the data point could exist anywhere in the comma-separated data. And that's where I run into this problem:

SELECT * FROM table WHERE csv_field LIKE '%men%'

Running this query gives me results for both women and men since men is a subset of women.

How can I run the query on "men" and receive records with data "women, boys, men, girls" -- and not records with data "women, boys, girls".

Appreciate any help you can offer!

You'll probably want to use regular expressions in your pattern match.

Have a look at the MySQL Regex

Your statement would look something like :

select * from table where csv_field rlike '[[:<:]]men[[:>:]]';


Ooooh that's interesting. I will look into that. REGEX is on my learning list this month!

I also was thinking something like this -- since I know my data structure:

SELECT * FROM table WHERE csv_field LIKE '%; men%' OR LIKE 'men%'

Which I did try and it's looking promising. But the REGEX way is much more bullet-proof (if I can get it right, that is....).


Try using word boundries, this should match men and not women


Word boundaries - mmmmmmmmm. Will give this a look-see as well. Thanks!

Theme © iAndrew 2016 - Forum software by © MyBB