How to only select numeric data from mysql?

问题:

Does mysql have a function like is_num() which can allow me determine the data is numeric or not?

回答1:

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?

---

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)

回答2:

In my case I just needed to check if data was >= 0 and not char, perhaps this is useful for you also:

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 | 
+--------+

回答3:

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:

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

Returns: "12"

select NumericOnly('abc987') as result;

Returns: "987"

(BTW, I wrote a post about this here)

回答4:

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.

Here's an example that assumes there are no decimal places in your data when it's numeric, so we can cast as DECIMAL.

This query select all the rows that contain numeric data in the is_it_a_number field.

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

回答5:

The simple syntax for IsNumeric from SQL to MYSQL is

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;

回答6:

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 ;