Using regex to to match text field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Using regex to to match text field

Post 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.
mintedjo
Forum Contributor
Posts: 153
Joined: Wed Nov 19, 2008 6:23 am

Re: Using regex to to match text field

Post by mintedjo »

I might be missing the point entirely, but why can't you use a LIKE statement?
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Using regex to to match text field

Post 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:
Post Reply