如何在 MySQL 中找到非 ASCII 字符?

2021-11-20 00:00:00 character-encoding mysql

我正在使用一个 MySQL 数据库，该数据库有一些从 Excel 导入的数据.数据包含非ASCII 字符(破折号等)以及隐藏的回车或换行.有没有办法使用 MySQL 找到这些记录?

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?

推荐答案

这完全取决于您定义的ASCII"，但我建议尝试这样的查询变体:

It depends exactly what you're defining as "ASCII", but I would suggest trying a variant of a query like this:

SELECT * FROM tableName WHERE columnToCheck NOT REGEXP '[A-Za-z0-9]';

该查询将返回 columnToCheck 包含任何非字母数字字符的所有行.如果您有其他可接受的字符，请将它们添加到正则表达式中的字符类中.例如，如果句号、逗号和连字符都可以，则将查询更改为:

That query will return all rows where columnToCheck contains any non-alphanumeric characters. If you have other characters that are acceptable, add them to the character class in the regular expression. For example, if periods, commas, and hyphens are OK, change the query to:

SELECT * FROM tableName WHERE columnToCheck NOT REGEXP '[A-Za-z0-9.,-]';

MySQL 文档最相关的页面可能是 12.5.2 正则表达式.

The most relevant page of the MySQL documentation is probably 12.5.2 Regular Expressions.

相关文章