Home > Database > Mysql Tutorial > How to Perform Diacritic Insensitive Searches in MySQL?

How to Perform Diacritic Insensitive Searches in MySQL?

Susan Sarandon
Release: 2024-11-01 12:38:02
Original
912 people have browsed it

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");
Copy after login

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';
Copy after login

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';
Copy after login

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';
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template