How to select integer values only from a varchar column in PostgreSQL

Question

How to select integer values only from a varchar column in PostgreSQL?

If the column contains:

abc
70
3g
71
1.5

I'd like to select only:

70
71

I'm struggling to find functions like: is_numeric, is_integer, to do something like this:

SELECT column
FROM table
WHERE isinteger(column)

Any ideas? Thank you.

score 12 · Accepted Answer · answered Mar 01 '13 at 17:57

12

SELECT column
FROM table
WHERE column ~ '^\d+$'

answered Mar 01 '13 at 17:57

Clodoaldo Neto

Thank you. That seems to work fine. Mind to share a link where I can find more info about the use of the `~` symbol? – Tom Mar 01 '13 at 19:29
2

@Tom Here is the [`link`](http://www.postgresql.org/docs/current/static/functions-matching.html) – Ihor Romanchenko Mar 01 '13 at 19:39
How do we include negative integers in the result as well? – Natarajan Chidhambharam Aug 31 '18 at 00:32

1 Answers1