如何从mysql中仅选择数字数据？

Question

MySQL是否有类似is_num()的函数，可允许我确定数据是数字还是非数字？

- user295515

Answer 1

您可能想创建一个用户自定义函数，将值与正则表达式匹配:

CREATE FUNCTION IsNumeric (sIn varchar(1024)) RETURNS tinyint 
   RETURN sIn REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$'; 

来源: MySQL论坛：Microsoft SQL Server:: MySQL中的IsNumeric()子句？

---

真值测试：

mysql> SELECT ISNUMERIC('1');
+----------------+
| ISNUMERIC('1') |
+----------------+
|              1 |
+----------------+
1 row in set (0.01 sec)

mysql> SELECT ISNUMERIC(25);
+---------------+
| ISNUMERIC(25) |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT ISNUMERIC('-100');
+-----------------+
| ISNUMERIC(-100) |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.03 sec)

mysql> SELECT ISNUMERIC('1.5');
+------------------+
| ISNUMERIC('1.5') |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT ISNUMERIC('-1.5');
+-------------------+
| ISNUMERIC('-1.5') |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.02 sec)

假值测试：

mysql> SELECT ISNUMERIC('a');
+----------------+
| ISNUMERIC('a') |
+----------------+
|              0 |
+----------------+
1 row in set (0.02 sec)

mysql> SELECT ISNUMERIC('a1');
+-----------------+
| ISNUMERIC('a1') |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT ISNUMERIC('10a');
+------------------+
| ISNUMERIC('10a') |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT ISNUMERIC('0.a');
+------------------+
| ISNUMERIC('0.a') |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT ISNUMERIC('a.0');
+------------------+
| ISNUMERIC('a.0') |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

- Daniel Vassallo

Answer 2

在我的情况中，我只需要检查数据是否 >= 0，而不是 char 类型，也许这对你也有用：

mysql> select '1'  REGEXP '^[0-9]+$' as result;
+--------+
| result |
+--------+
|      1 | 
+--------+
1 row in set (0.16 sec)

mysql> select '1a'  REGEXP '^[0-9]+$' as result;
+--------+
| result |
+--------+
|      0 | 
+--------+

- gonzalemario

Answer 3

如果您想从字符串中选择仅数字字符，请尝试使用以下代码：

CREATE FUNCTION IsNumeric (val varchar(255)) RETURNS tinyint 
 RETURN val REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

CREATE FUNCTION NumericOnly (val VARCHAR(255)) 
 RETURNS VARCHAR(255)
BEGIN
 DECLARE idx INT DEFAULT 0;
 IF ISNULL(val) THEN RETURN NULL; END IF;

 IF LENGTH(val) = 0 THEN RETURN ""; END IF;

 SET idx = LENGTH(val);
  WHILE idx > 0 DO
  IF IsNumeric(SUBSTRING(val,idx,1)) = 0 THEN
   SET val = REPLACE(val,SUBSTRING(val,idx,1),"");
   SET idx = LENGTH(val)+1;
  END IF;
  SET idx = idx - 1;
  END WHILE;
  RETURN val;
 END;

---

通过像这样调用NumericOnly函数来使用它：

select NumericOnly('1&2') as result;

返回："

12

"

select NumericOnly('abc987') as result;

返回："987"

（顺便说一下，我在这里写了一篇关于此的文章）

- VenerableAgents

---

谢谢您提供的解决方案，但是有一个打字错误。请将以下代码更改为：SET val= REPLACE(val,".",""); 然后它就像魔术一样奏效了。 - user787128

@Raj - 我已经删除了那行代码。我认为它没有帮助，因为它最终会在“WHILE”循环中被删除。 - VenerableAgents

Answer 4

SQL到MYSQL的IsNumeric的简单语法如下：

sql:

SELECT isNumeric(col_name) FROM Table where isNumeric(col_name) <> 0

MySQL

select concat('',col_name * 1) from table where concat('',col_name * 1) <> 0;

- NiKhil Kutewalla

Answer 5

只需使用DECIMAL或SIGNED / UNSIGNED将数据显式转换为数字，并使用STRCMP与原始数据值进行比较。如果STRCMP返回0（相等），则知道您有一个数字。否则，它不是。

这里是一个示例，假设在数据为数字时没有小数位，因此我们可以将其转换为DECIMAL。

此查询选择包含is_it_a_number字段中的数字数据的所有行。

SELECT * FROM my_table WHERE
    STRCMP(CAST(my_table.is_it_a_number AS DECIMAL(10,0)), my_table.is_it_a_number) = 0;

- Tom Auger

Answer 6

快上千倍。

DROP FUNCTION IF EXISTS NumericOnly;
DELIMITER $$
CREATE FUNCTION NumericOnly(val VARCHAR(25)) RETURNS VARCHAR(25)
BEGIN
 DECLARE idx INT DEFAULT 0;
  IF ISNULL(val) THEN RETURN NULL; END IF;
  IF LENGTH(val) = 0 THEN RETURN ""; END IF;

 SET idx = LENGTH(val);
  WHILE idx > 0 DO
  IF (SUBSTRING(val,idx,1) in ('0','1','2','3','4','5','6','7','8','9')) = 0 THEN
   SET val = CONCAT(SUBSTRING(val, 1, idx-1), SUBSTRING(val FROM idx + 1));
  END IF;
  SET idx = idx - 1;
  END WHILE;
  RETURN val;
END;
$$
DELIMITER ;

- Laurent Bauchau