MySQL - 从varchar列中仅选择数字值

Question

考虑以下表格：

create table mixedvalues (value varchar(50));

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

我该如何编写一个select语句只返回数字值，例如

100
200
300
400
500

SQLFiddle

- Wes

Answer 1

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

- Strawberry

---

不要忘记可能存在负数，正则表达式为^-?[0-9]+$。 - Tom Auger

如果该列存储的数据如下：abc mnop prqs xyz 71.6789 30.7654，它是否有效？ - Nisha

@Nisha 不是的。这是一个不同的问题。 - Strawberry

我们如何增强它以使其也适用于十进制值？ - Aqua 4

@Aqua4 我认为这本身就是一个问题。 - Strawberry

Answer 2

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

参考：MySQL中检测数值类型的方法

- Slowcoder

---

这个解决方案目前可行，但会给你一个“警告：#1292”。 - Adriano

Answer 3

你差不多就要成功了：

SELECT * 
FROM mixedvalues 
WHERE value > 0;

SQLFiddle

- Patrick Q

---

这比其他答案简单得多。它是如何工作的？ - Wes

这取决于字母永远不会出现在数字之后的事实。 - Strawberry

好的，谢谢。我注意到它对于像600ABC这样的东西会失败。 - Wes

这不是一个合适的选择，可能会在以后产生问题。 - markthewizard1234

这个答案是不正确的。 使用这个查询将返回'1ABCABC'，显然不是一个数值。 - John

不仅如此，所有那些无法转换的值都会引起警告。这并不是问题，除非你在严格模式下运行... - Nancy Deschênes

Answer 4

列表项包含字符串，后跟数字。

$string = "Test";

select * from table where columnname REGEXP "$string-*[0-9]+"; 

- Sreejith N

---

如果您想搜索具有特定小数位的数字：([0-9]){2,3} 可以匹配从10到999的数字。 - allanlaal

Answer 5

SELECT * FROM mixedvalues 
WHERE value > 0 
ORDER BY CAST(value as SIGNED INTEGER) ASC

- Jit

Answer 6

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

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

- user5577526

---

问题是关于 MySQL 的。 - Maelish

此函数在MYSQL中不存在。 - Aqua 4