The following PostgreSQL examples return only those rows that have numeric values 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 All Numeric Values
We can use the following query to return all numeric values from the above table:
SELECT c1
FROM t1
WHERE c1 ~ '^[-+]?[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
Return Integers
If we only want to return integers, the query can be a lot simpler:
SELECT c1
FROM t1
WHERE c1 ~ '^[0-9]+$';
Result:
c1 ---- 0 1
Contains Numeric Data
If we want to find rows that contain numeric data (even if they also contain non-numeric data), we can do the following:
SELECT c1
FROM t1
WHERE c1 ~ '[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
In PostgreSQL, ~
is a case-sensitive operator for matching the given regular expression. For case-insensitive matches, use ~*
.
You can use !~
to return all rows that don’t match the regular expression (and !~*
for case-insensitive matches).
POSIX Character Classes
Postgres also supports POSIX character classes. So we can use [:digit:]
instead of [0-9]
if we prefer.
Example:
SELECT c1
FROM t1
WHERE c1 ~ '^[[:digit:]]?$';
Result:
c1 ---- 0 1