MySQL &quot;REGEXP&quot; queries

MySQL "REGEXP" queries

rsterbin

I haven't had any luck with this, maybe someone here can help:

I'm trying to use MySQL REGEX to find particular words in TEXT fields, and I've had some success, but only with case-sensitive searches. I can't seem to find a way to make case-insensitive searches any way other than replacing 'a' with '[Aa]' and so on.

SELECT comments FROM clients REGEXP "[[:<:]]music[[:>:]]"

finds "save the music" but not "Music preferences".

Does anyone know how to signify a case-insensitive search short of
"[[:<]][Mm][Uu][Ss][Ii][Cc][[:>:]]"?

Thanks,

Reha

badman

hi
it would be better if you had stated your objective of doing so, makes it easy to suggest alternatives!

what i gather is that you are trying to do some kind of search...right? if yeah , read on....

You can use a select query as following

select comments from clients where comments like "%music%"

this will return both the cases mentioned by you
cheers!

rsterbin

Well, it turns out I was mistaken about REGEXP being case-sensitive. After reading rj's reply, I took a look at the LIKE page in the manual and discovered somewhat inadvertently the answer to my question--REGEX is case-insensitive by default, a fact they neglected to mention on the REGEXP page. So in fact:

SELECT title FROM test WHERE title REGEX "[[:<:]]webby[[:space:]]awards[[:>:]]";

returns both "posthumous webby awards" and "Fifth Annual Webby Awards", not just the former as I had thought.

Learn something new every day...

-Reha

Anon

Reha wrote:

REXEXP is case-sensitive in versions of MySQL prior to 3.23.4 - as I found out to my cost when creating a search function on my local dev box and then uploading to my ISP, who tend to lag on the MySQL front 🙁

See <a href="http://www.mysql.com/doc/R/e/Regexp.html"> http://www.mysql.com/doc/R/e/Regexp.html</a>...