MySQL LIKE/RegExp help needed

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!


I have a table with a column that contains many names, like "Ant Simon

if I was looking for one name in the string of names I would do...
SELECT * from classroom where name LIKE '%ant%'.

But the select above will return rows with names like 'antoine' or

So I guess I could have a select with spaces...
SELECT * from classroom where name LIKE '% ant %'.

But that also does not work if 'ant' is the first or last entry in the list,
(like in the example above).

So to solve my problem I would need something like...
FROM classroom
       name LIKE "ant %"        //    first item in the list with more
OR name LIKE "% ant %"    //    neither the first nor last item in the list
OR name LIKE "% ant"        //    the last entry in the list
OR name = "ant"            //    the only entry in the list

But that does not look right, it does the job I guess but not in a very
elegant way.

Can someone give me a RegExp that would look for an exact name, (case
insensitive), in a string of names.
And how would I escape special characters in the string itself, (in weird
cases where the name I am looking for is something like "An^t"

Many thanks in advance,


Site Timeline