How to Perform Diacritic-Insensitive Search for Spanish Accents in MySQL?

MySQL: Diacritic Insensitive Search in Spanish Accents

Original Question:

How to perform a diacritic insensitive search on a MySQL database for words containing Spanish accents (áéíóú)?

Response:

To achieve diacritic insensitive search in MySQL for Spanish accents, leverage character sets and collations. The initial character set and collation can affect the outcome of a search.

Steps:

1. Set the character set to "latin1" or "utf8" using the SET NAMES command.
2. Execute your query.
3. Specify the appropriate character set for non-Unicode data using _utf8 in the query.

Example:

With "latin1" character set:

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

With "utf8" character set:

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

With "latin1" character set using _utf8 for non-Unicode data:

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

By adjusting the character set and collation, you can enable diacritic insensitive searches for Spanish accented words in your MySQL database.

The above is the detailed content of How to Perform Diacritic-Insensitive Search for Spanish Accents in MySQL?. For more information, please follow other related articles on the PHP Chinese website!