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.
Please buff my shoes: RLIKE & LIKE
Moderator: General Moderators
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 trythe spaces will force to search on the word. Not the best way!
An easy way to match only the word is to try
Code: Select all
..... LIKE '% buff %'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.
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.