CODEWITHSUNDEEP
sqlregexoracle-database
jackie21
jackie21

Reputation: 337

Filter on numerical values in Oracle SQL

I would like to filter only on numerical values 0-9 from a list of values that are in VARCHAR format

column:postal_Code
dhf65784ja!
s73;'235fsa
(kadf8(*45--

Expected Output
65784
73235
845

I tried the following:

SELECT distinct  
    postal_Code

FROM xx

where 1=1
and postal_Code!~ '[A-Z]' 
and postal_Code!~ '[a-z]' 
and postal_Code!~ '[-_º~@!"./#%£^$]'

However this does not give the right output.

Upvotes: 0

Views: 60

Answers (1)

Guilherme Martin
Guilherme Martin

Reputation: 847

You can use regexp_replace

Example:

select regexp_replace(postal_Code, '[^0-9]', '') from xx;

Upvotes: 3

Related Questions