How can I return all numeric values in PostgreSQL?-CodePudding

I had to select from the column all the values that contain numbers (NULL values aren't interested). How can I make a query to my table using something like this:

SELECT c1
FROM t1 
WHERE c1 ...;

I tried to find suitable regular expressions, which are popular in many imperative programming languages, but they didn't fit for PostgreSQL, unfortunately (or I used them incorrectly)

CodePudding user response：

Possible solution:

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

CodePudding user response：

From Pattern Matching:


SELECT
    fld
FROM (
    VALUES ('1'),
        ('test1'),
        ('34'),
        ('dog'),
        ('3cat')) AS t (fld)
WHERE
    regexp_match(fld, '^[[:digit:]]{1,}$') IS NOT NULL;

fld 
-----
 1
 34

CodePudding user response：

Another possibility:

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

From this table named STACKOVERFLOW:

id_stack	name
1	Niklaus Wirth
2	Bjarne Stroustrup
3	Linus Torvalds
4	Donald Knuth
5	C3PO
6	R2D2
7	ZX Spectrum 3

The Query SELECT NAME FROM STACKOVERFLOW WHERE NAME ~ '[0-9]'; will return:

name
C3PO
R2D2
ZX Spectrum 3