The following MariaDB examples return only those rows that have numeric values in a given column.

These examples use the REGEXP function to return values that match the specified pattern.

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

Return Integers

If we only want to return integers, the query 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

MariaDB 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:]]?$';

Result:

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