How Can I Identify Non-ASCII Characters in My MySQL Database?

How to Find Non-ASCII Characters in MySQL

In a MySQL database filled with data imported from Excel, the presence of non-ASCII characters and hidden carriage returns or line feeds can create challenges. To address this issue, MySQL offers robust character set management capabilities.

To pinpoint records containing these characters, you can use the following query:

SELECT whatever
FROM tableName
WHERE columnToCheck <> CONVERT(columnToCheck USING ASCII)

This query leverages the CONVERT(col USING charset) function to convert non-convertible characters into replacement characters. As a result, the unconverted and converted text will differ, allowing you to identify the problematic records.

For further insight into character set management in MySQL, refer to the documentation: https://dev.mysql.com/doc/refman/8.0/en/charset-repertoire.html.

Additionally, you can utilize other character set names instead of ASCII. For instance, to detect characters incompatible with code page 1257 (used by Lithuanian, Latvian, and Estonian), you can use the following:

CONVERT(columnToCheck USING cp1257)

The above is the detailed content of How Can I Identify Non-ASCII Characters in My MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!