How to Identify Non-ASCII Characters (Including Hidden Returns & Line Feeds) in a MySQL Database?

Identifying Non-ASCII Characters in MySQL Databases

Question:

How can we locate records containing non-ASCII characters, including hidden carriage returns and line feeds, within a MySQL database populated with data imported from Excel?

Answer:

MySQL offers advanced character set management capabilities that facilitate addressing this issue.

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

The CONVERT function replaces unconvertable characters with special markers, creating disparities between the converted and unconverted text, making non-ASCII characters readily identifiable.

MySQL allows for the use of various character sets with the CONVERT function. For instance, to detect characters that may not display correctly in the cp1257 character set, employ:

CONVERT(columnToCheck USING cp1257)

For more insights on character set management in MySQL, refer to:

https://dev.mysql.com/doc/refman/8.0/en/charset-repertoire.html

The above is the detailed content of How to Identify Non-ASCII Characters (Including Hidden Returns & Line Feeds) in a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!