只从varchar列中选择数值

Consider the following table :

考虑下表:

create table mixedvalues (value varchar(50));

insert into mixedvalues values ('100');
insert into mixedvalues values ('ABC100');
insert into mixedvalues values ('200');
insert into mixedvalues values ('ABC200');
insert into mixedvalues values ('300');
insert into mixedvalues values ('ABC300');
insert into mixedvalues values ('400');
insert into mixedvalues values ('ABC400');
insert into mixedvalues values ('500');
insert into mixedvalues values ('ABC500');

How can I write a select statement that would only return the numeric values like

如何编写只返回数值的select语句

100
200
300
400
500

SQLFiddle

4 个解决方案

#1

SELECT * 
FROM mixedvalues 
WHERE value REGEXP '^[0-9]+$';

#2

You were close :

你是亲密:

SELECT * 
FROM mixedvalues 
WHERE value > 0;

SQLFiddle

SQLFiddle

#3

SELECT * 
FROM mixedvalues 
WHERE concat('',value * 1) = value;

Reference: Detect if value is number in MySQL

引用:检测MySQL中的值是否为number

#4

You can filter your result set using the ISNUMERIC function:

您可以使用ISNUMERIC函数来过滤结果集:

SELECT value
FROM #mixedvalues 
where ISNUMERIC(value)=1