在MySQL中检测值是否为number

这在大多数情况下都是可行的。

SELECT * FROM myTable WHERE concat('',col1 * 1) = col1

不适合非标准的数字，比如

• 1e4
• 1.2e5
• 123.(尾随小数)

你也可以使用正则表达式…就像:

SELECT * FROM myTable WHERE col1 REGEXP '^[0-9]+$';

< >强引用: http://dev.mysql.com/doc/refman/5.1/en/regexp.html < / p >

如果你的数据是" test " " test0 " " test1111 " " 111test " " 111 "

选择所有数据类型为简单整型的记录:

SELECT *
FROM myTable
WHERE col1 REGEXP '^[0-9]+$';

结果:‘111’

(在正则表达式中，^表示开始，$表示结束)

使用实例选择存在整数或十进制数的所有记录。

SELECT *
FROM myTable
WHERE col1 REGEXP '^[0-9]+\\.?[0-9]*$'; - for 123.12

结果:'111'(与上一个示例相同)

最后,选择number存在的所有记录，使用:

SELECT *
FROM myTable
WHERE col1 REGEXP '[0-9]+';

结果:'test0'和'test1111'和'111test'和'111'

在我的计算机上，另一个似乎比REGEXP更快的替代方法是

SELECT * FROM myTable WHERE col1*0 != col1;

这将选择col1以数值开头的所有行。

这个答案与Dmitry类似，但它允许小数以及正数和负数。

select * from table where col1 REGEXP '^[[:digit:]]+$'

我建议:如果你的搜索很简单，你可以使用'

column*1 = column

' operator interesting:)是工作，并且比varchar/char字段更快

SELECT * FROM myTable WHERE column = 1;

ABC*1 => 0 (NOT EQU **ABC**)
AB15*A => 15 (NOT EQU **AB15**)
15AB => 15 (NOT EQU **15AB**)
15 => 15 (EQUALS TRUE **15**)

SELECT * FROM myTable WHERE sign (col1)!=0

当然sign(0)是0，但是你可以把你的查询限制为…

SELECT * FROM myTable WHERE sign (col1)!=0 or col1=0

UPDATE:这不是100%可靠，因为“1abc”将返回符号 1，但是“ab1c”将返回0…因此，这只适用于不以数字开头的文本

仍然缺少这个简单的版本:

SELECT * FROM myTable WHERE `col1` + 0 = `col1`

(加法应该比乘法快)

或者更慢的版本:

SELECT *,
CASE WHEN `col1` + 0 = `col1` THEN 1 ELSE 0 END AS `IS_NUMERIC`
FROM `myTable`
HAVING `IS_NUMERIC` = 1

你可以使用CAST

  SELECT * from tbl where col1 = concat(cast(col1 as decimal), "")

使用UDF(用户定义函数)。

CREATE FUNCTION isnumber(inputValue VARCHAR(50))
RETURNS INT
BEGIN
IF (inputValue REGEXP ('^[0-9]+$'))
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;

那么当你询问时

select isnumber('383XXXX')

——返回0

select isnumber('38333434')

——返回1

select isnumber(mycol) mycol1, col2, colx from table; ——对列mycol1

-你可以增强功能，采取小数，科学记数法，等等…

使用UDF的优点是可以在“where子句”比较的左侧或右侧使用它。这在发送到数据库之前极大地简化了SQL:

 SELECT * from tablex where isnumber(columnX) = isnumber('UnkownUserInput');

希望这能有所帮助。

SELECT * FROM myTable
WHERE col1 REGEXP '^[+-]?[0-9]*([0-9]\\.|[0-9]|\\.[0-9])[0-9]*(e[+-]?[0-9]+)?$'

也会匹配带符号的小数(比如-1.2， +0.2, 6。， 2e9, 1.2e-10)。

测试:

drop table if exists myTable;
create table myTable (col1 varchar(50));
insert into myTable (col1)
values ('00.00'),('+1'),('.123'),('-.23e4'),('12.e-5'),('3.5e+6'),('a'),('e6'),('+e0');


select
col1,
col1 + 0 as casted,
col1 REGEXP '^[+-]?[0-9]*([0-9]\\.|[0-9]|\\.[0-9])[0-9]*(e[+-]?[0-9]+)?$' as isNumeric
from myTable;

结果:

col1   |  casted | isNumeric
-------|---------|----------
00.00  |       0 |         1
+1     |       1 |         1
.123   |   0.123 |         1
-.23e4 |   -2300 |         1
12.e-5 | 0.00012 |         1
3.5e+6 | 3500000 |         1
a      |       0 |         0
e6     |       0 |         0
+e0    |       0 |         0

Demo

尝试除法/1

select if(value/1>0 or value=0,'its a number', 'its not a number') from table

我发现这很有效

if(col1/col1= 1,'number',col1) AS myInfo

返回数字行

我发现解决方案与以下查询和工作为我:

SELECT * FROM myTable WHERE col1 > 0;

这个查询返回的行只有大于0的数字列col1

返回非数字行

如果你想检查列不是数字，试试这个技巧(!col1 > 0):

SELECT * FROM myTable WHERE !col1 > 0;

你可以使用正则表达式的更多细节https://dev.mysql.com/doc/refman/8.0/en/regexp.html

我使用这个^([,|.]?[0-9])+$。这个函数允许对小数和浮点数进行句柄处理

SELECT
*
FROM
mytable
WHERE
myTextField REGEXP "^([,|.]?[0-9])+$"