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!
Simple Regex query hopefully
Moderator: General Moderators
Re: Simple Regex query hopefully
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:
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:
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.
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]"(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]"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
If I understand correctly, the names are comma-separated in the "admin" column. Try this:
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.
Code: Select all
SELECT * FROM `users` WHERE `admin` REGEXP '[[:<:]]jbloggs[[:>:]]'
or
SELECT * FROM `users` WHERE `admin` REGEXP '(^|, )jbloggs($|, )'
Re: Simple Regex query hopefully
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
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
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.
Wishing you all a fun day.
And nice to see you.
According to aba's link, the funny delimiter are word boundaries, so it would be the same as \bjbloggs\b in PCRE.Can you explain the first expression?
Yes, in regex, dollars are people too!I like the second one, never thought of using ^ and $ in an OR situation!
Wishing you all a fun day.
-
abareplace
- Forum Newbie
- Posts: 9
- Joined: Fri Jan 06, 2012 1:43 am
Re: Simple Regex query hopefully
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!
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
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
-Greg