MySQL diacritic insensitive search (spanish accents)

Solution 1:

Character sets & collations, not my favorites, but they DO work:

mysql> SET NAMES latin1;
mysql> SELECT 'lápiz' LIKE 'lapiz';
+-----------------------+
| 'lápiz' LIKE 'lapiz' |
+-----------------------+
|                     0 | 
+-----------------------+
1 row in set (0.01 sec)

mysql> SET NAMES utf8;
mysql> SELECT 'lápiz' LIKE 'lapiz';
+-----------------------+
| 'lápiz' LIKE 'lapiz' |
+-----------------------+
|                     1 | 
+-----------------------+


mysql> SET NAMES latin1;
mysql> SELECT _utf8'lápiz' LIKE _utf8'lapiz' ;
+---------------------------------+
| _utf8'lápiz' LIKE _utf8'lapiz' |
+---------------------------------+
|                               1 | 
+---------------------------------+

A nice chapter to read in the manual:Character Set Support

Solution 2:

If you set the table's charset to UTF-8 and the collation to utf8_*_ci (_ci means "case insensitive) MySQL will perform case and accent-insensitive searches by default

Read more about charsets and collations here:
http://dev.mysql.com/doc/refman/5.1/en/charset-charsets.html

I tested it and

"lapiz" matches: "lápiz," "lapíz," and "lapiz"
"nino" matches: "niño," "ninó," and "nino"

You can set up the collation of your table upon creation:

CREATE TABLE table ( ... )
CHARACTER SET uft8 COLLATE utf8_general_ci;

Or you can ALTER it if it already exists.For more info, read the manual (link above).
If you are using phpMyAdmin, you can select the collation when you create your table.

Solution 3:

You can force the column name to convert as UTF8. I haven't tried is for Spanish but rather for Romanian characters with accents, but I assume it's the same thing.

The query I use is:

SELECT CONVERT('gîgă' USING utf8) LIKE '%giga%'

Or in the more likely case of looking up a column in a table, you can use:

SELECT CONVERT(column_name USING utf8) FROM table_name LIKE '%giga%'