The following PostgreSQL examples return only those rows that don’t have a numeric value in a given column.

Sample Data

Let’s create a table with sample data:

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

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

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

Result:

    c1     
-----------
 12.e-3
 a
 9afc
 e7
 +e0
 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 !~ '^[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

Doesn’t Contain Numeric Data

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

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

Result:

 c1  
-----
 a
 Ten

In PostgreSQL, !~ is a case-sensitive operator that is used for returning values that don’t match the given regular expression. For case-insensitive matches, use !~*.

You can use ~ to return all rows that do match the regular expression (and ~* for case-insensitive matches).