Thursday, October 23, 2008

LIKE is not case insensitive?

I was stuck by understanding the following SQL query:


SELECT name
FROM my_table
WHERE name LIKE '%Jack%'

There is nothing special. It just tries to pick up all "names" of "my_table", those values contain the word "Jack" (case sensitive). However, "jack" is also in the result. I cannot believe my eyes.

I googled "LIKE case insensitive" and got the answer:
The problem is caused by the collation of the database. The default collation of my database is "latin1_swedish_ci". The trailing "_ci" stands for case insensitive. I changed it to "utf8_bin", the problem has been solved.

Here are some useful links:
http://forums.mysql.com/read.php?20,202839,202839
http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html
http://dev.mysql.com/doc/refman/5.0/en/charset-charsets.html

1 comment:

Lex Y. Li said...

IMHO "full text search" feature should be used in such a case.