MySQL 如何只从mysql中选择数字数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2756736/
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
How to only select numeric data from mysql?
提问by user295515
Does mysql have a function like is_num() which can allow me determine the data is numeric or not?
mysql 是否有像 is_num() 这样的函数可以让我确定数据是否为数字？
回答by Daniel Vassallo
You may want to create a user defined function that matches the value against a regular expression:
您可能想要创建一个用户定义的函数来匹配正则表达式的值：
CREATE FUNCTION IsNumeric (sIn varchar(1024)) RETURNS tinyint
RETURN sIn REGEXP '^(-|\+){0,1}([0-9]+\.[0-9]*|[0-9]*\.[0-9]+|[0-9]+)$';
Source: MySQL Forums :: Microsoft SQL Server :: IsNumeric() clause in MySQL?
来源：MySQL 论坛 :: Microsoft SQL Server :: MySQL 中的 IsNumeric() 子句？
Truthy Tests:
真实测试：
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)
Falsy Tests:
假测试：
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)
回答by gonzalemario
In my case I just needed to check if data was >= 0 and not char, perhaps this is useful for you also:
就我而言，我只需要检查数据是否 >= 0 而不是字符，也许这对您也有用：
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 |
+--------+
回答by VenerableAgents
If you want to select only the numeric characters from your string, try using this:
如果您只想从字符串中选择数字字符，请尝试使用以下命令：
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;
Use it by calling the NumericOnly function like this:
通过像这样调用 NumericOnly 函数来使用它：
select NumericOnly('1&2') as result;
Returns: "12"
返回：“12”
select NumericOnly('abc987') as result;
Returns: "987"
返回：“987”
(BTW, I wrote a post about this here)
回答by Tom Auger
Just convert your data explicitly to a number using DECIMAL or SIGNED/UNSIGNED, and use STRCMP to compare to the original data value. If STRCMP returns 0 (equality) then you know you have a number. Otherwise, it's not.
只需使用 DECIMAL 或 SIGNED/UNSIGNED 将您的数据显式转换为数字，然后使用 STRCMP 与原始数据值进行比较。如果 STRCMP 返回 0（相等），那么您知道您有一个数字。否则，它不是。
Here's an example that assumes there are no decimal places in your data when it's numeric, so we can cast as DECIMAL.
这是一个示例，假设您的数据是数字时没有小数位，因此我们可以将其转换为 DECIMAL。
This query select all the rows that contain numeric data in the is_it_a_number field.
此查询选择在 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;
回答by NiKhil Kutewalla
The simple syntax for IsNumeric from SQL to MYSQL is
IsNumeric 从 SQL 到 MYSQL 的简单语法是
sql:
sql:
SELECT isNumeric(col_name) FROM Table where isNumeric(col_name) <> 0
Mysql
mysql
select concat('',col_name * 1) from table where concat('',col_name * 1) <> 0;
回答by Laurent Bauchau
Thousand times faster.
快上千倍。
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 ;
