CODEWITHSUNDEEP
postgresql
WhiteFoRestever
WhiteFoRestever

Reputation: 23

How can I return all numeric values in PostgreSQL?

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)

Upvotes: 2

Views: 199

Answers (4)

Alexey Ivanov
Alexey Ivanov

Reputation: 13

SELECT * FROM <table> WHERE <column> ~ 'PRF:\d{11}-\d{4}'

Upvotes: 0

The_Student
The_Student

Reputation: 47

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

Upvotes: 1

Adrian Klaver
Adrian Klaver

Reputation: 19665

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

Upvotes: 1

M. Kuz.
M. Kuz.

Reputation: 73

Possible solution:

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

Upvotes: 1

Related Questions