Page 1 of 1

Please buff my shoes: RLIKE & LIKE

Posted: Wed Feb 12, 2003 9:33 pm
by McGruff
I'm writing my first search script and I want to be able to find a word wherever it appears in a field.

If I search for LIKE 'buff' it will only find columns = "buff" but NOT those with "please buff my shoes".

If I search for LIKE '%buff%' I get "please buff my shoes" OK, but also anything with "rebuffed" or "buffy" - which I don't want.

So, I tried RLIKE which, according to the manual, looks for a string anywhere within a field - different to the behaviour of LIKE with no wildcards which (I think) is looking for an exact match.

However, RLIKE '^buff$' seems to be behaving same as LIKE 'buff' ie it will find fields with "buff" but NOT "please buff my shoes".

Is there a way to find single words with a mysql query? I could probably process the initial mysql results down to what I want in php but that's going to create a lot of overhead with a big result set.

Posted: Wed Feb 12, 2003 9:50 pm
by Mr. Tech
This is the code I use that might help:

$sql = "select * from tablename where (this rlike '$buff')";

That help?

Posted: Thu Feb 13, 2003 5:29 am
by Wayne
Your problem was due to the fact that ^ matches the beginning of a field and $ matches the end of a field not the beginning and ending of individual words in the field. To do that you will have to use more complex regular expressions.

An easy way to match only the word is to try

Code: Select all

..... LIKE '% buff %'
the spaces will force to search on the word. Not the best way!

Posted: Thu Feb 13, 2003 11:19 am
by McGruff
Thanks for all your help. I'm getting closer...

Seems daft, Wayne, that RLIKE ^ and $ wildcards are programmed to look at the start/end of fields - would have been more useful if they stuck to start/end of the search term.

Damnit, why can't computers just do what you want them to do and stop mucking about? My DOG has a better understanding of context. He's not quite so hot at dynamic content management though, to be fair.

Posted: Thu Feb 13, 2003 8:11 pm
by McGruff
Duh - tried a forum search and found something which works perfectly:

RLIKE '[[:<:]]" . $value . "[[:>:]]'

-------------
search term stuff - ignore

search problem
trouble with search
finding words
searching for unique word in text field
mysql search
matching search terms