Page 1 of 1

Using regex to to match text field

Posted: Mon Dec 22, 2008 7:00 pm
by alex.barylski
Basically I need to search a field with a comma separated list of states...

A quick Google revealed MySL supports some POSIX regex:

Code: Select all

SELECT 'Monty!' REGEXP 'm%y%%';
How would I parse the field which contains the states as CSV and match only those records which have a state that matches.

Here is a demo record:

pkid, fkid, name, age, address, states:
1, 2, "Alex", 29, "123 Test Ave.", "Texas, Alabama, Utah"

The query would look something like:

[sql]SELECT * FROM TABLE WHERE age < 30 REGEX '%$state'[/sql]

Where $state would be a single state...

Anyone mind showing me how this might be done using regex and a single field?

I know I could introduce another table which associated multiple states with a single record but I need to use regex for the sake of simplicity.

Re: Using regex to to match text field

Posted: Mon Dec 22, 2008 8:48 pm
by mintedjo
I might be missing the point entirely, but why can't you use a LIKE statement?

Re: Using regex to to match text field

Posted: Mon Dec 22, 2008 9:33 pm
by alex.barylski
LIKE would potentially match...ummmm...actually...

I guess that would work....so long as the full state name was provided, eh?

Hmmm...let me try it...thanks for sense of it all. :lol: