Please buff my shoes: RLIKE & LIKE

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Please buff my shoes: RLIKE & LIKE

Post 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.
Mr. Tech
Forum Contributor
Posts: 205
Joined: Tue Feb 11, 2003 4:18 pm
Location: Australia

Post by Mr. Tech »

This is the code I use that might help:

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

That help?
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post 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!
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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
Post Reply