Reg Ex help!

[CS-Jay]

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]

ive never done regex with mysql, sorry cant help

[CS-Jay]

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

[Tom_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]

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

$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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

it would have been that easy!

[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.

[sarettah]

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]

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]

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 !!!]

[sarettah]

[CS-Jay]

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]

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]

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]

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]

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]

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]

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]

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]

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

[darksoul]

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]

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]

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