MySQL diacritic insensitive search (spanish accents)

Question

Hello,

I have a MySQL database with words containing accents in Spanish (áéíóú). I'd like to know if there's any way to do a diacritic insensitive search. For instance, if I search for "lapiz" (without accent), i'd like to get results containing the word "lápiz" from my db. The way I'm currently doing the query is as follows:

$result = mysql_query("SELECT * FROM $lookuptable WHERE disabled = '0' AND name LIKE '%$q%' OR productCode LIKE '%$q%' LIMIT $sugglimit");

This is for an online store, so I don't know what people will be searching for... "lapiz" is just and example.

Thanks!

Answer 1

Store a second version of the string that has been stripped of diacritics?

Answer 2

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

Answer 3

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.