not case sensitive query in mysql

Question

i need to query my database and find results :

mysql_query("select * from ".ALU_TABLE." where username like '%$q%' or name like '%$q%'");

if i have a name in my table such as Book and i enter book in search box it wont show the Book

i need to query my database as not to be case sensitive.

possible duplicate of [case insensitive for sql LIKE wildcard statement](http://stackoverflow.com/questions/2876789/case-insensitive-for-sql-like-wildcard-statement) — OMG Ponies, Jun 12 '10 at 17:07
Also: http://stackoverflow.com/search?q=mysql+case+insensitive — OMG Ponies, Jun 12 '10 at 17:07

score 4 · Accepted Answer · answered Jun 12 '10 at 13:49

4

You can use the LOWER() function

... WHERE LOWER(username) LIKE blabl OR LOWER(name) LIKE asdasd

answered Jun 12 '10 at 13:49

Bart van Heukelom

2

This works. But you will not allow the db to use indexes if you mention the column names as parameters to LOWER(), so it might be quite slow. – O. Jones Jun 12 '10 at 14:12

score 3 · Answer 2 · answered Jun 12 '10 at 13:46

3

You need to append something like COLLATE utf8_general_ci to your query. (The _ci suffix stands for case insensitive.)

With the COLLATE clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements.

answered Jun 12 '10 at 13:46

aioobe

Your database may already be set up for case-insensitive collation. – O. Jones Jun 12 '10 at 14:10

2 Answers2