CODEWITHSUNDEEP
postgresqlselectnumbersrowdigits
user2089671
user2089671

Reputation: 21

How to select data whose column_name data has digits only in PostgreSQL?

How to select row data whose column_name has digits only in PostreSQL?

Like when table has:

ID | Column_name
 1 | 000012
 2 | ABC001

The expected result on select should only be the ID 1 wherein the Column_name has only digits.

I have tried this code but does not work:

select *
from table
Where not Column_name like '%[^0-9]%'

Can anybody give me advice? Thanks

Upvotes: 0

Views: 1414

Answers (2)

Tomas Greif
Tomas Greif

Reputation: 22653

You can use function trim:

 where trim(a,'0123456789') = ''

, but regex is probably better solution.

Upvotes: 0

mu is too short
mu is too short

Reputation: 434685

A regex is probably the easiest thing:

where column_name ~ E'^\\d+$'

See the POSIX Regular Expressions section of the manual for details.

Upvotes: 2

Related Questions