How Can We Return Only Numeric Values in MySQL?

This tutorial about returning only Numeric Values in MySQL will make us understand how we can implicate that while dealing with  databases. Please see below we will walk you through to get only Numeric Values in MySQL.

First we have to enter sample data in our table.

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');

The column is a varchar(255) column and this means that I will have all kinds of both numeric and characters. Here is our query to get all numeric values.

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

If we want to get only integer values, here is the query.

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

If we want to get values which contains numeric data then here is the query.

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

We can also use POSIX character class.

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

If you have any questions, please write to us.