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! |
Welcome Guest, Not a member yet? Register Sign In |