Thursday, October 23, 2008

LIKE is not case insensitive?

I was stuck by understanding the following SQL query:

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:,202839,202839

1 comment:

Lex Y. Li said...

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