CODEWITHSUNDEEP
sqlpostgresql
avi
avi

Reputation: 1846

How to strip all characters except numbers from text in PostgreSQL?

I have a text column:

id   authorization
--------------------
 1   A1541-57
 2   135
 3   G-ff5
 4   BLA
 5   120G3

I want to write a query which shows the column but only with numbers, if there are no numbers show null

This is my desired output:

1 154157
2 135
3 5
4
5 1203

I know this could be done with regular expressions but I'm not sure how I do that in a query...

Upvotes: 3

Views: 2754

Answers (1)

user330315
user330315

Reputation:

A regex is the way to go:

select id, regexp_replace(authorization, '[^0-9]', '', 'g')
from the_table;

[^0-9] means "anything that is not a digit

Upvotes: 4

Related Questions