CodeIgniter Forums
A MySQL query question - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: A MySQL query question (/showthread.php?tid=14535)

A MySQL query question - El Forum - 01-06-2009

[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!

A MySQL query question - El Forum - 01-06-2009

[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

A MySQL query question - El Forum - 01-06-2009

[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!

A MySQL query question - El Forum - 01-06-2009

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

Code:
\bmen


A MySQL query question - El Forum - 01-06-2009

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