MySQL - 仅从 varchar 列中选择数值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22510736/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 20:16:11 来源:igfitidea点击:
MySQL - Select only numeric values from varchar column
提问by Wes
Consider the following table :
考虑下表：
create table mixedvalues (value varchar(50));
insert into mixedvalues values
('100'),
('ABC100'),
('200'),
('ABC200'),
('300'),
('ABC300'),
('400'),
('ABC400'),
('500'),
('ABC500');
How can I write a select statement that would only return the numericvalues like
我如何编写一个只返回数值的选择语句，如
100
200
300
400
500
SQLFiddle
SQLFiddle
回答by Strawberry
SELECT *
FROM mixedvalues
WHERE value REGEXP '^[0-9]+$';
回答by Slowcoder
SELECT *
FROM mixedvalues
WHERE concat('',value * 1) = value;
Reference: Detect if value is number in MySQL
参考： 检测MySQL中的值是否为数字
回答by Jit
SELECT * FROM mixedvalues
WHERE value > 0
ORDER BY CAST(value as SIGNED INTEGER) ASC
回答by user5577526
You can filter your result set using the ISNUMERICfunction:
您可以使用以下ISNUMERIC函数过滤结果集：
SELECT value
FROM #mixedvalues
where ISNUMERIC(value)=1
