If you ever encounter a character column that should be a numeric column, there’s always a possibility that it contains non-numeric data that you don’t know about.

In MariaDB, you can run a query like the following to return non-numeric data from the 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;

That SELECT statement at the end results in this:

+-----------+
| 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 Non-Numeric Values

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

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

Result:

+-----------+
| c1        |
+-----------+
| 12.e-3    |
| a         |
| 9afc      |
| Ten       |
| 5 Dollars |
+-----------+

Return Non-Integers

If we only want to return non-integers, the query can be a lot simpler:

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

Result:

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

Does Not Contain Numeric Data

If we just want to find rows that don’t contain any numeric data, we can do the following:

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

Result:

+------+
| c1   |
+------+
| a    |
| Ten  |
+------+

We can use a POSIX class to get the same result:

SELECT c1
FROM t1 
WHERE c1 NOT REGEXP '[[:digit:]]';

Result:

+------+
| c1   |
+------+
| a    |
| Ten  |
+------+