The following MySQL query returns only those rows that have numeric values in a given column.

Sample Data

Suppose we create a table like this:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
    c1 varchar(255)
);

INSERT INTO t1 (c1) VALUES 
    ('0'),
    ('1'),
    ('+1'),
    ('-1'),
    ('00.00'),
    ('73.45'),
    ('+73.45'),
    ('-73.45'),
    ('.246'),
    ('-.34e7'),
    ('12.e-3'),
    ('1.2e+4'),
    ('a'),
    ('9afc'),
    ('e7'),
    ('+e0'),
    ('Ten'),
    ('5 Dollars');

SELECT * FROM t1;

The table has now been created and contains the following data:

+-----------+
| c1        |
+-----------+
| 0         |
| 1         |
| +1        |
| -1        |
| 00.00     |
| 73.45     |
| +73.45    |
| -73.45    |
| .246      |
| -.34e7    |
| 12.e-3    |
| 1.2e+4    |
| a         |
| 9afc      |
| e7        |
| +e0       |
| Ten       |
| 5 Dollars |
+-----------+

The column is a varchar(255) column, so it’s not numeric. It can (and does) contain numbers but these are stored as character data. It can also contain arbitrary text (which it does).

Return All Numeric Values

We can use the following query to return all numeric values from the above table:

SELECT c1
FROM t1 
WHERE c1 REGEXP '^[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$';

Result:

+--------+
| c1     |
+--------+
| 0      |
| 1      |
| +1     |
| -1     |
| 00.00  |
| 73.45  |
| +73.45 |
| -73.45 |
| .246   |
| -.34e7 |
| 1.2e+4 |
| e7     |
| +e0    |
+--------+

Here, we used the REGEXP function to match only the values that match our specified pattern.

Return Integers

If we only want to return integers, the pattern can be a lot simpler:

SELECT c1
FROM t1 
WHERE c1 REGEXP '^[0-9]+$';

Result:

+------+
| c1   |
+------+
| 0    |
| 1    |
+------+

Contains Numeric Data

If we want to find rows that merely contain numeric data (even if they also contain non-numeric data), we can do the following:

SELECT c1 
FROM t1 
WHERE c1 REGEXP '[0-9]+';

Result:

+-----------+
| c1        |
+-----------+
| 0         |
| 1         |
| +1        |
| -1        |
| 00.00     |
| 73.45     |
| +73.45    |
| -73.45    |
| .246      |
| -.34e7    |
| 12.e-3    |
| 1.2e+4    |
| 9afc      |
| e7        |
| +e0       |
| 5 Dollars |
+-----------+

POSIX Character Classes

MySQL supports POSIX character classes, which means we can use [:digit:] instead of [0-9] in our regular expressions.

Example:

SELECT c1
FROM t1 
WHERE c1 REGEXP '^[[:digit:]]?$';

Which is the equivalent of the following:

SELECT c1
FROM t1 
WHERE c1 REGEXP '^[0-9]?$';