How to Perform Diacritic Insensitive Searches in MySQL?

MySQL Diacritic Insensitive Search

When working with Spanish words that contain accents, it can be challenging to perform search queries while accounting for diacritic marks. This article provides a solution to execute diacritic insensitive searches in a MySQL database using a combination of character sets and collations.

The example provided:

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

does not account for diacritic differences and may exclude results containing words like "lápiz" when searching for "lapiz."

To achieve diacritic insensitivity, one can utilize the MySQL command SET NAMES to specify a specific character set and collation for the database connection. By setting the character set to latin1 and the collation to an accent-insensitive one, the query can match both words with and without accents.

Example:

SET NAMES latin1;
SELECT 'lápiz' LIKE 'lapiz';

This query will return 0, indicating that "lápiz" and "lapiz" are not equal.

By switching to the utf8 character set:

SET NAMES utf8;
SELECT 'lápiz' LIKE 'lapiz';

The query will return 1, demonstrating that the search is now diacritic insensitive.

To explicitly specify that the pattern should match a UTF-8 string, one can use the _utf8 prefix:

SET NAMES latin1;
SELECT _utf8'lápiz' LIKE _utf8'lapiz';

In this example, the query will return 1, confirming diacritic insensitivity even when explicitly using the latin1 character set.

For further understanding, refer to the MySQL documentation on Character Set Support.

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