Reg Ex help! - GoFuckYourself.com

CS-Jay · 04-06-2011, 01:06 PM

Hey all, I'm having a tough time with some regular expressions, and was wondering if someone could lend a hand.

I am starting off with a phrase:

quinton jackson

Then I want to compare the phrase:

quinton rampage jackson

To see if they match. I'm actually trying to do this out of mysql command line right now, with little luck.

I can get the first part, regex (^q.[\s]) but not match anything else.

I am totally lost on regex for some reason. Any help would be great!

fris · 04-06-2011, 01:29 PM

ive never done regex with mysql, sorry cant help

CS-Jay · 04-06-2011, 01:35 PM

well, it doesn't matter where it's at. We could talk about php if you wanted.

Tom_PM · 04-06-2011, 01:36 PM

In you example would you want that to be considered a match or not? I'm not so great at regex, but I wasn't sure from your question is why I asked.

Kiopa_Matt · 04-06-2011, 01:36 PM

I'm not sure what you're trying to do. From what you said, here:

Code:

$string1 = "quinton jackson";
$string2 = "quinton rampage jackson";

if ($string1 != $string2) {
    echo "Nope, don't match";
} else { 
    echo "Yep, match";
}

Or let us know what you're trying to do...

CS-Jay · 04-06-2011, 01:40 PM

I'm actually trying to do a partial match.

If it's partially right, I can pull up a row from the db.

Kiopa_Matt · 04-06-2011, 01:46 PM

Ohhh, you're doing a search engine. In that case, regex isn't exactly what you're looking for, and it's a little more complex than that. You have to index the keywords / tags / phrases within the database, then upon a user searching, split up the phrase as needed, and search the database for any (partially) matching results. Then write a relevancy algorithm, etc.

You're best off either just using an existing free PHP search engine out there, or having a developer take care of this for you.

sarettah · 04-06-2011, 02:06 PM

Data set in a table called testit, fieldname name.

quinton jackson
quinton rampage jackson
Bill smith
quinton smith
bill jackson
quenton jockson

SELECT * FROM testit WHERE name REGEXP 'quinton.*jackson'

Returns:

quinton jackson
quinton rampage jackson

SELECT * FROM testit WHERE name REGEXP 'quinton.*'

Returns:

quinton jackson
quinton rampage jackson
quinton smith

select * from testit where name regexp 'q.*j.*'

Returns:

quinton jackson
quinton rampage jackson
quenton jockson

SELECT * FROM `testit` WHERE name REGEXP '.*j.*'

Returns:

quinton jackson
quinton rampage jackson
bill jackson
quenton jockson

Do any of those do what you want to do? Those are using extremely simple patterns, pattern matching can get extremely complex.

CS-Jay · 04-06-2011, 02:07 PM

well, i guess you can call it a search engine. I didn't really think of it that way. More or less, I wanted to see if I had a partial match, if not, go ahead and update the db with the new name kinda deal.

CS-Jay · 04-06-2011, 02:16 PM

Quote:

Originally Posted by sarettah

Data set in a table called testit, fieldname name.
select * from testit where name regexp 'q.*j.*'

Returns:

quinton jackson
quinton rampage jackson
quenton jockson

I can't believe it was that easy. Maybe I have read way to much on regex, and the brain overloaded. That is basically what I wanted to do. Just simple partial matching of names! Thank you!

sarettah · 04-06-2011, 02:23 PM

Quote:

Originally Posted by CS-Jay

I can't believe it was that easy. Maybe I have read way to much on regex, and the brain overloaded. That is basically what I wanted to do. Just simple partial matching of names! Thank you!

No prob.

Regx stuff can get real hairy real quick imho ;p

btw, for readability sakes when I am doing that in mysql I usually use the rlike function instead of regexp (it is the same function under a different name) so the sql statement becomes something like:

select * from tablename where fieldname rlike 'regex expression'

select * from testit where name rlike 'q.*j.*' Just reads better than select * from testit where name regexp 'q.*j.*' imho.

sarettah · 04-06-2011, 02:24 PM

Quote:

Originally Posted by Kiopa_Matt

Ohhh, you're doing a search engine. In that case, regex isn't exactly what you're looking for, and it's a little more complex than that. You have to index the keywords / tags / phrases within the database, then upon a user searching, split up the phrase as needed, and search the database for any (partially) matching results. Then write a relevancy algorithm, etc.

You're best off either just using an existing free PHP search engine out there, or having a developer take care of this for you.

There are many ways to do a serach engine type search of a database other than that. Depends on what your data looks like as to which methodology works best.

CS-Jay · 04-06-2011, 02:27 PM

Yes I've been around the rlike pages on mysql docs a few times today.

Speaking of getting hairy fast, I was doing things like looking for spaces, checking to see if the last space wasn't there, even boundaries, you name it. It truly is a black art!

CS-Jay · 04-06-2011, 02:41 PM

One think I am looking at here:
What I am looking for:
norther arizona

What I am getting returned, but not wanted:
north carolina

select * from testit where name rlike 'nort.*ar.*'

how can specify the "ar" is to start the word. ^ only does the start of the string right? my query:

select * from testit where name rlike 'nort.*^ar.*'

returns nothing.

sarettah · 04-06-2011, 02:47 PM

Quote:

Originally Posted by CS-Jay

One think I am looking at here:
What I am looking for:
norther arizona

What I am getting returned, but not wanted:
north carolina

select * from testit where name rlike 'nort.*ar.*'

how can specify the "ar" is to start the word. ^ only does the start of the string right? my query:

select * from testit where name rlike 'nort.*^ar.*'

returns nothing.

select * from testit where name rlike 'nort.* ar.*'

Just put a space after the first wildcard then the second string has to start with the first letter specified in this case a.

Data set:

North Carolina
North Arizona
Northern Arizona

Returns

North Arizona
Northern Arizona

CS-Jay · 04-06-2011, 02:53 PM

it would have been that easy!

V_RocKs · 04-06-2011, 03:59 PM

You could also look up "mysql match against" and see how you can do the same thing with built in functions to get the same results with a limit 1 if you only want to top result.

sarettah · 04-06-2011, 04:48 PM

Quote:

Originally Posted by V_RocKs

You could also look up "mysql match against" and see how you can do the same thing with built in functions to get the same results with a limit 1 if you only want to top result.

Ah full text searching. Yeah, that has it's own set of weird little things ;p

CS-Jay · 04-06-2011, 05:12 PM

I also thought, it's been about a month since I tried to use it, that match against uses whole words as well. Not just parts of words.

I did use it, and it was not what I needed.

I'm off to the races now, with sarettah's much needed simple advice.

fris · 04-06-2011, 05:32 PM

Quote:

Originally Posted by CS-Jay

I also thought, it's been about a month since I tried to use it, that match against uses whole words as well. Not just parts of words.

I did use it, and it was not what I needed.

I'm off to the races now, with sarettah's much needed simple advice.

whats this for exactly, some sort of implmentation for searching on cs?

DangerX !!! · 04-06-2011, 09:50 PM

sarettah · 04-07-2011, 03:10 AM

CS-Jay · 04-07-2011, 05:07 AM

Quote:

Originally Posted by fris

whats this for exactly, some sort of implmentation for searching on cs?

Kinda. More like related content kinda deal. We have to give the users what they want!

fris · 04-07-2011, 05:23 AM

Quote:

Originally Posted by CS-Jay

Kinda. More like related content kinda deal. We have to give the users what they want!

SELECT post_content FROM test WHERE tagline like '%$search_word%'

maybe something like this?

CS-Jay · 04-07-2011, 05:26 AM

Using the regex example, or rlike above is exactly what I needed. That way if there are extra words in a phrase, I can kick it out, and plurals/abbreviations get included in the search. I format out the text before I send it to the db as well.

CS-Jay · 04-07-2011, 09:03 AM

Ok, I'm back, now I need to figure out how to terminate the search:
what I want:
norther arizona
not
norther arizona flagstaff

select * from testit where name rlike 'nort.* ar.*[^ ]'

doesnt' work. I've tried \s in there, which is for whitespace (\n, \r, etc), But not sure about ascii space.

I'm sure it's a totally simple answer that I cannot figure out.

sarettah · 04-07-2011, 09:26 AM

Quote:

Originally Posted by CS-Jay

Ok, I'm back, now I need to figure out how to terminate the search:
what I want:
norther arizona
not
norther arizona flagstaff

select * from testit where name rlike 'nort.* ar.*[^ ]'

doesnt' work. I've tried \s in there, which is for whitespace (\n, \r, etc), But not sure about ascii space.

I'm sure it's a totally simple answer that I cannot figure out.

SELECT * FROM `testit` WHERE name RLIKE 'n.* ar.*ona$' That will tell the query that it has to end in 'ona'

Not sure if that is what you need, starting to question how you will pull this off programmatically.

CS-Jay · 04-07-2011, 09:34 AM

I'm questioning it as well.

Ok, the thing is, I may only have this information:

norther ariz

but I know I do not want norther "arizona flagstaff" because for that I would get:

north ariz flag

It's cool, I'll keep researching. Learning more and more about something I have avoided for years.

Thanks again

sarettah · 04-07-2011, 09:42 AM

You can also pull it off with:

SELECT * FROM `testit` WHERE name RLIKE 'n.* ar.{0,10}$'

The .{0,10} tells it to match any characters up to 10 chars long and the $ tells it that the string should end at that point.

if we just use the .{0,10} it will still match the flagstaff entry because it dose not really care where the string ends.

Or better yet (there were 5 minutes between the last sentence and this ;p) I think what you really want is this:

SELECT * FROM `testit` WHERE name RLIKE 'n.* ar.*[blank]$' Which will tell it to get all characters for the second part of the string that start with 'ar' and go to the first blank space.

From: http://dev.mysql.com/doc/refman/5.5/en/regexp.html

Character Class Name Meaning
alnum Alphanumeric characters
alpha Alphabetic characters
blank Whitespace characters
cntrl Control characters
digit Digit characters
graph Graphic characters
lower Lowercase alphabetic characters
print Graphic or space characters
punct Punctuation characters
space Space, tab, newline, and carriage return
upper Uppercase alphabetic characters
xdigit Hexadecimal digit characters

You could also use [space] instead of blank.

CS-Jay · 04-07-2011, 09:59 AM

Again, works!

I also found out that mysql does not do many regex features, like lookahead "?". I was getting some errors with that.

Thanks again! Believe it or not, I have learned a crazy amount of regex in the last day or so. I never wanted to fuck with it, but now, I feel many more times comfortable with it.

darksoul · 04-07-2011, 10:12 AM

If I understand correctly you're looking for:
SELECT * FROM testit WHERE name RLIKE '^nort.*ariz[[:alnum:]]+$';

darksoul · 04-07-2011, 10:14 AM

Quote:

Originally Posted by sarettah

SELECT * FROM `testit` WHERE name RLIKE 'n.* ar.*[blank]$' Which will tell it to get all characters for the second part of the string that start with 'ar' and go to the first blank space.

It won't match 'norther arizona' (without an ending space)

CS-Jay · 04-07-2011, 10:26 AM

You nailed that one, the alnum is the key to matching it w/o a ending space. I was just going over the mysql docs page, trying that shit out. Was looking at char class.

sarettah · 04-07-2011, 11:09 AM

forget it, I misread the post...lol ;p

04-06-2011, 01:06 PM	#1
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	Reg Ex help! Hey all, I'm having a tough time with some regular expressions, and was wondering if someone could lend a hand. I am starting off with a phrase: quinton jackson Then I want to compare the phrase: quinton rampage jackson To see if they match. I'm actually trying to do this out of mysql command line right now, with little luck. I can get the first part, regex (^q.[\s]) but not match anything else. I am totally lost on regex for some reason. Any help would be great! __________________ I do stuff - aIm CS_Jay_D

04-06-2011, 01:29 PM	#2
fris Too lazy to set a custom title Industry Role: Join Date: Aug 2002 Posts: 55,359	ive never done regex with mysql, sorry cant help __________________ Since 1999: 69 Adult Industry awards for Best Hosting Company and professional excellence. WP Stuff

04-06-2011, 01:35 PM	#3
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	well, it doesn't matter where it's at. We could talk about php if you wanted. __________________ I do stuff - aIm CS_Jay_D

04-06-2011, 01:36 PM	#4
Tom_PM Porn Meister Industry Role: Join Date: Feb 2005 Posts: 16,443	In you example would you want that to be considered a match or not? I'm not so great at regex, but I wasn't sure from your question is why I asked. __________________ 43-922-863 Shut up and play your guitar.

04-06-2011, 01:36 PM	#5
Kiopa_Matt Confirmed User Industry Role: Join Date: Oct 2007 Posts: 1,448	I'm not sure what you're trying to do. From what you said, here: Code: $string1 = "quinton jackson"; $string2 = "quinton rampage jackson"; if ($string1 != $string2) { echo "Nope, don't match"; } else { echo "Yep, match"; } Or let us know what you're trying to do... __________________ xMarkPro -- Ultimate Blog Network Management Streamline your marketing operations. Centralize management of domains, pages, Wordpress blogs, sponsors, link codes, media items, sales and traffic statistics, plus more!

04-06-2011, 01:40 PM	#6
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	I'm actually trying to do a partial match. If it's partially right, I can pull up a row from the db. __________________ I do stuff - aIm CS_Jay_D

04-06-2011, 01:46 PM	#7
Kiopa_Matt Confirmed User Industry Role: Join Date: Oct 2007 Posts: 1,448	Ohhh, you're doing a search engine. In that case, regex isn't exactly what you're looking for, and it's a little more complex than that. You have to index the keywords / tags / phrases within the database, then upon a user searching, split up the phrase as needed, and search the database for any (partially) matching results. Then write a relevancy algorithm, etc. You're best off either just using an existing free PHP search engine out there, or having a developer take care of this for you. __________________ xMarkPro -- Ultimate Blog Network Management Streamline your marketing operations. Centralize management of domains, pages, Wordpress blogs, sponsors, link codes, media items, sales and traffic statistics, plus more!

04-06-2011, 02:06 PM	#8
sarettah see you later, I'm gone Industry Role: Join Date: Oct 2002 Posts: 14,091	Data set in a table called testit, fieldname name. quinton jackson quinton rampage jackson Bill smith quinton smith bill jackson quenton jockson SELECT * FROM testit WHERE name REGEXP 'quinton.jackson' Returns: quinton jackson quinton rampage jackson SELECT FROM testit WHERE name REGEXP 'quinton.' Returns: quinton jackson quinton rampage jackson quinton smith select from testit where name regexp 'q.j.' Returns: quinton jackson quinton rampage jackson quenton jockson SELECT * FROM `testit` WHERE name REGEXP '.j.' Returns: quinton jackson quinton rampage jackson bill jackson quenton jockson Do any of those do what you want to do? Those are using extremely simple patterns, pattern matching can get extremely complex. __________________ All cookies cleared!

04-06-2011, 02:07 PM	#9
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	well, i guess you can call it a search engine. I didn't really think of it that way. More or less, I wanted to see if I had a partial match, if not, go ahead and update the db with the new name kinda deal. __________________ I do stuff - aIm CS_Jay_D

04-06-2011, 02:27 PM	#13
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	Yes I've been around the rlike pages on mysql docs a few times today. Speaking of getting hairy fast, I was doing things like looking for spaces, checking to see if the last space wasn't there, even boundaries, you name it. It truly is a black art! __________________ I do stuff - aIm CS_Jay_D

04-06-2011, 02:41 PM	#14
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	One think I am looking at here: What I am looking for: norther arizona What I am getting returned, but not wanted: north carolina select * from testit where name rlike 'nort.ar.' how can specify the "ar" is to start the word. ^ only does the start of the string right? my query: select * from testit where name rlike 'nort.^ar.' returns nothing. __________________ I do stuff - aIm CS_Jay_D

04-06-2011, 02:53 PM	#16
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	it would have been that easy! __________________ I do stuff - aIm CS_Jay_D

04-06-2011, 03:59 PM	#17
V_RocKs Damn Right I Kiss Ass! Industry Role: Join Date: Dec 2003 Location: Cowtown, USA Posts: 32,406	You could also look up "mysql match against" and see how you can do the same thing with built in functions to get the same results with a limit 1 if you only want to top result.

04-06-2011, 05:12 PM	#19
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	I also thought, it's been about a month since I tried to use it, that match against uses whole words as well. Not just parts of words. I did use it, and it was not what I needed. I'm off to the races now, with sarettah's much needed simple advice. __________________ I do stuff - aIm CS_Jay_D

04-06-2011, 09:50 PM	#21
DangerX !!! Confirmed User Industry Role: Join Date: Feb 2011 Location: La Isla Bonita Power Level: ❤❤❤❤❤❤❤❤❤❤ Posts: 886	__________________ This is sig area!

04-07-2011, 03:10 AM	#22
sarettah see you later, I'm gone Industry Role: Join Date: Oct 2002 Posts: 14,091	__________________ All cookies cleared!

04-07-2011, 05:26 AM	#25
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	Using the regex example, or rlike above is exactly what I needed. That way if there are extra words in a phrase, I can kick it out, and plurals/abbreviations get included in the search. I format out the text before I send it to the db as well. __________________ I do stuff - aIm CS_Jay_D

04-07-2011, 09:03 AM	#26
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	Ok, I'm back, now I need to figure out how to terminate the search: what I want: norther arizona not norther arizona flagstaff select * from testit where name rlike 'nort.* ar.*[^ ]' doesnt' work. I've tried \s in there, which is for whitespace (\n, \r, etc), But not sure about ascii space. I'm sure it's a totally simple answer that I cannot figure out. __________________ I do stuff - aIm CS_Jay_D

04-07-2011, 09:34 AM	#28
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	I'm questioning it as well. Ok, the thing is, I may only have this information: norther ariz but I know I do not want norther "arizona flagstaff" because for that I would get: north ariz flag It's cool, I'll keep researching. Learning more and more about something I have avoided for years. Thanks again __________________ I do stuff - aIm CS_Jay_D

04-07-2011, 09:42 AM	#29
sarettah see you later, I'm gone Industry Role: Join Date: Oct 2002 Posts: 14,091	You can also pull it off with: SELECT * FROM `testit` WHERE name RLIKE 'n.* ar.{0,10}$' The .{0,10} tells it to match any characters up to 10 chars long and the $ tells it that the string should end at that point. if we just use the .{0,10} it will still match the flagstaff entry because it dose not really care where the string ends. Or better yet (there were 5 minutes between the last sentence and this ;p) I think what you really want is this: SELECT * FROM `testit` WHERE name RLIKE 'n.* ar.[blank]$' Which will tell it to get all characters for the second part of the string that start with 'ar' and go to the first blank space. From: http://dev.mysql.com/doc/refman/5.5/en/regexp.html Character Class Name Meaning alnum Alphanumeric characters alpha Alphabetic characters blank Whitespace characters cntrl Control characters digit Digit characters graph Graphic characters lower Lowercase alphabetic characters print Graphic or space characters punct Punctuation characters space Space, tab, newline, and carriage return upper Uppercase alphabetic characters xdigit Hexadecimal digit characters You could also use [space] instead of blank. __________________ All cookies cleared! Last edited by sarettah; 04-07-2011 at 09:45 AM..*

04-07-2011, 09:59 AM	#30
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	Again, works! I also found out that mysql does not do many regex features, like lookahead "?". I was getting some errors with that. Thanks again! Believe it or not, I have learned a crazy amount of regex in the last day or so. I never wanted to fuck with it, but now, I feel many more times comfortable with it. __________________ I do stuff - aIm CS_Jay_D

04-07-2011, 10:12 AM	#31
darksoul Confirmed User Join Date: Apr 2002 Location: /root/ Posts: 4,997	If I understand correctly you're looking for: SELECT * FROM testit WHERE name RLIKE '^nort.*ariz[[:alnum:]]+$'; __________________ 1337 5y54\|)m1n: 157717888 BM-2cUBw4B2fgiYAfjkE7JvWaJMiUXD96n9tN Cambooth

04-07-2011, 10:26 AM	#33
CS-Jay Confirmed User Join Date: Oct 2003 Location: Command Central, West Palm Beach, Fl Posts: 1,794	You nailed that one, the alnum is the key to matching it w/o a ending space. I was just going over the mysql docs page, trying that shit out. Was looking at char class. __________________ I do stuff - aIm CS_Jay_D

04-07-2011, 11:09 AM	#34
sarettah see you later, I'm gone Industry Role: Join Date: Oct 2002 Posts: 14,091	forget it, I misread the post...lol ;p __________________ All cookies cleared! Last edited by sarettah; 04-07-2011 at 11:11 AM..