Simple Regex query hopefully

Any questions involving matching text strings to patterns - the pattern is called a "regular expression."

Moderator: General Moderators

Post Reply
jlhall
Forum Newbie
Posts: 1
Joined: Mon Jan 09, 2012 6:16 am

Simple Regex query hopefully

Post by jlhall »

Im hoping im asking this question in the right place.

I have a mysql table with a column admin.

An example of contents in Row1 could be "jbloggs, fbloggs"
An example of contents in Row 2 could be "jbloggs1"

What i want to do is display Rows based on a username.

The issue is if i do a search LIKE "%jbloggs%" its going to display both Row1 and Row2

Is there a way using Regex that i can match the exact username?

I was thinking i could get the length of the string then match the username and length but not sure how id go about doing that.

Apologies if the question is a bit unclear!
User avatar
ragax
Forum Commoner
Posts: 85
Joined: Thu Dec 15, 2011 1:40 pm
Location: Nelson, NZ

Re: Simple Regex query hopefully

Post by ragax »

Hi jhall,

Not sure I fully understand your question, and I'm not a mySQL regex expert.
MySQL regex syntax is quite poor compared to PHP's PCRE. Nevertheless, it works.
To give you an example, this works for me in sqlYog:

Code: Select all

SELECT * FROM movies WHERE url REGEXP "[0-9]"
This selects movies where the url field contains a digit.
(Note that in mySQL regex, \d doesn't work, therefore using a [0-9] class. Could have used [[:digit:]] instead.)

So for you, something like:

Code: Select all

SELECT * FROM YourTable WHERE Row2 REGEXP "jbloggs[0-9]"
should get you on the right track (selects records where row2 is jbloggs1, jbloggs2 etc).
If you give me more details of what you are trying to accomplish, I am happy to have a look for you. :)
abareplace
Forum Newbie
Posts: 9
Joined: Fri Jan 06, 2012 1:43 am

Re: Simple Regex query hopefully

Post by abareplace »

If I understand correctly, the names are comma-separated in the "admin" column. Try this:

Code: Select all

SELECT * FROM `users` WHERE `admin` REGEXP '[[:<:]]jbloggs[[:>:]]'

or

SELECT * FROM `users` WHERE `admin` REGEXP '(^|, )jbloggs($|, )'
The first regex uses word boundaries, the second one uses "beginning of the line or comma", "end of the line or comma". You can read about the syntax in MySQL docs.
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Simple Regex query hopefully

Post by twinedev »

Hey, I like the second one, never thought of using ^ and $ in an OR situation!

Can you explain the first expression? I'm not understanding it, even after throwing it into RegexBuddy and trying both of the POSSIX settings. (Love that program!)

One thing I would suggest for the second one, not being able to see the actual data, in case there are not spaces, do (^|, ?) and ($|, ?) to say the space is optional.

-Greg
User avatar
ragax
Forum Commoner
Posts: 85
Joined: Thu Dec 15, 2011 1:40 pm
Location: Nelson, NZ

Re: Simple Regex query hopefully

Post by ragax »

Now, with the post by abareplace, I see what the OP wanted. I'd go for the first solution then. Way to go, aba!
And nice to see you. :D
Can you explain the first expression?
According to aba's link, the funny delimiter are word boundaries, so it would be the same as \bjbloggs\b in PCRE.
I like the second one, never thought of using ^ and $ in an OR situation!
Yes, in regex, dollars are people too! :wink:

Wishing you all a fun day.
abareplace
Forum Newbie
Posts: 9
Joined: Fri Jan 06, 2012 1:43 am

Re: Simple Regex query hopefully

Post by abareplace »

Hi, Twinedev,

as Playful already said, [[:<:]] and [[:>:]] are beginning of a word and end of a word (\< \> or \b in other flavors).

Optional spaces are great idea, thank you!
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Simple Regex query hopefully

Post by twinedev »

Yep, after I saw Playful's post, I went back to the link again, just didn't get it read all the way to the bottom I guess (or got interrupted in the middle when I was checking it)

-Greg
Post Reply