How can I find non-ASCII characters in MySQL?

mysql character-encoding
Ed Mays picture Ed Mays · Dec 30, 2008 · Viewed 116.7k times · Source

I'm working with a MySQL database that has some data imported from Excel. The data contains non-ASCII characters (em dashes, etc.) as well as hidden carriage returns or line feeds. Is there a way to find these records using MySQL?

Answer

O. Jones picture O. Jones · Jul 31, 2012

MySQL provides comprehensive character set management that can help with this kind of problem.

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

The CONVERT(col USING charset) function turns the unconvertable characters into replacement characters. Then, the converted and unconverted text will be unequal.

See this for more discussion. https://dev.mysql.com/doc/refman/8.0/en/charset-repertoire.html

You can use any character set name you wish in place of ASCII. For example, if you want to find out which characters won't render correctly in code page 1257 (Lithuanian, Latvian, Estonian) use CONVERT(columnToCheck USING cp1257)

Related questions

How do I see what character set a MySQL database / table / column is?

What is the (default) charset for: MySQL database MySQL table MySQL column

Read More
Change MySQL default character set to UTF-8 in my.cnf?

Currently we are using the following commands in PHP to set the character set to UTF-8 in our application. Since this is a bit of overhead, we'd like to set this as the default setting in MySQL. Can we do …

Read More
How to convert an entire MySQL database characterset and collation to UTF-8?

How can I convert entire MySQL database character-set to UTF-8 and collation to UTF-8?

Read More