仅选择 MySQL 中仅包含字母数字字符的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1471523/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT only rows that contain only alphanumeric characters in MySQL
提问by code_burgar
I'm trying to select all rows that contain only alphanumeric characters in MySQL using:
我正在尝试使用以下方法选择 MySQL 中仅包含字母数字字符的所有行：
SELECT * FROM table WHERE column REGEXP '[A-Za-z0-9]';
However, it's returning all rows, regardless of the fact that they contain non-alphanumeric characters.
但是，它会返回所有行，而不管它们包含非字母数字字符。
回答by Aaron Digulla
Try this code:
试试这个代码：
SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$'
This makes sure that all characters match.
这可以确保所有字符都匹配。
回答by John Kugelman
Your statement matches any string that contains a letter or digit anywhere, even if it contains other non-alphanumeric characters. Try this:
您的语句匹配在任何地方包含字母或数字的任何字符串，即使它包含其他非字母数字字符。尝试这个：
SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$';
^and $require the entire string to match rather than just any portion of it, and +looks for 1 or morealphanumberic characters.
^并$要求匹配整个字符串而不是其中的任何部分，并+查找 1 个或多个字母数字字符。
You could also use a named character class if you prefer:
如果您愿意，也可以使用命名字符类：
SELECT * FROM table WHERE column REGEXP '^[[:alnum:]]+$';
回答by Yannick Motton
Try this:
尝试这个：
REGEXP '^[a-z0-9]+$'
As regexp is not case sensitive except for binary fields.
除了二进制字段外，regexp 不区分大小写。
回答by apollo
There is also this:
还有这个：
select m from table where not regexp_like(m, '^[0-9]\d+$')
which selects the rows that contains characters from the column you want (which is m in the example but you can change).
它从您想要的列中选择包含字符的行（在示例中为 m，但您可以更改）。
Most of the combinations don't work properly in Oracle platforms but this does. Sharing for future reference.
大多数组合在 Oracle 平台中无法正常工作，但确实如此。分享以供日后参考。
回答by Akshay Singh
Try this
尝试这个
select count(*) from table where cast(col as double) is null;
回答by INTERESTING FACTS
Change the REGEXPto Like
更改REGEXP为Like
SELECT * FROM table_name WHERE column_name like '%[^a-zA-Z0-9]%'
this one works fine
这个很好用
