How can I return all numeric values in PostgreSQL?

Question

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)

M. Kuz. · Accepted Answer · 2022-12-11 18:22:24Z

1

Possible solution:

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

answered Dec 11, 2022 at 18:22

M. Kuz.

736 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adrian Klaver · Accepted Answer · 2022-12-11 18:44:15Z

1

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

edited Dec 11, 2022 at 18:44

answered Dec 11, 2022 at 18:25

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

1 Comment

WhiteFoRestever Over a year ago

Thank you for your answer, but the third value (in example) is "34", this query returns "{3}", but I need "34". The above answer from M. Kuz. looks true.

The_Student · Accepted Answer · 2022-12-11 19:01:39Z

1

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

edited Dec 11, 2022 at 19:01

answered Dec 11, 2022 at 18:54

The_Student

478 bronze badges

Comments

Alexey Ivanov · Accepted Answer · 2022-12-22 17:48:18Z

0

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

answered Dec 22, 2022 at 17:48

Alexey Ivanov

136 bronze badges

1 Comment

Jeremy Caney Over a year ago

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, can you edit your answer to include an explanation of what you're doing and why you believe it is the best approach?

Collectives™ on Stack Overflow

How can I return all numeric values in PostgreSQL?

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related