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.

Clodoaldo Neto · Accepted Answer · 2013-03-01 17:57:50Z

13

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

answered Mar 1, 2013 at 17:57

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

3 Comments

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 Here is the link

How do we include negative integers in the result as well?