如何在MySQL中找到非ASCII字符？ [英] How can I find non-ASCII characters in 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 NOT columnToCheck 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 NOT columnToCheck REGEXP '[A-Za-z0-9.,-]';

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

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

这篇关于如何在MySQL中找到非ASCII字符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！