Page 1 of 1

Simple Regex query hopefully

Posted: Mon Jan 09, 2012 6:31 am
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!

Re: Simple Regex query hopefully

Posted: Mon Jan 09, 2012 1:31 pm
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. :)

Re: Simple Regex query hopefully

Posted: Mon Jan 09, 2012 6:53 pm
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.

Re: Simple Regex query hopefully

Posted: Tue Jan 10, 2012 12:54 am
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

Re: Simple Regex query hopefully

Posted: Tue Jan 10, 2012 2:14 am
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.

Re: Simple Regex query hopefully

Posted: Tue Jan 10, 2012 6:34 am
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!

Re: Simple Regex query hopefully

Posted: Tue Jan 10, 2012 9:06 am
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