A MySQL query question

[eluser]tinawina[/eluser]
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:

Code:

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:

Code:

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!

[eluser]Hockeychap[/eluser]
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[[:>:]]';

Cheers
Justin

[eluser]tinawina[/eluser]
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:

Code:

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....).

Thanks!

[eluser]Popcorn[/eluser]
Try using word boundries, this should match men and not women

Code:

\bmen

[eluser]tinawina[/eluser]
Word boundaries - mmmmmmmmm. Will give this a look-see as well. Thanks!