Oracle: Replacing non-numeric chars in a string

Question

I have a field in my database where users have saved free-form telephone numbers. As a result, the data has all sorts of different formatting:

(area) nnn-nnnn
area-nnn-nnnn
area.nnn.nnnn
etc

I would like to strip out all the non-numeric characters and just store the digits, but I can't find a simple way to do this. Is it possible without using one REPLACE for each char?

Álvaro González · Accepted Answer · 2014-01-16 16:00:47Z

67

You can use REGEXP_REPLACE since Oracle 10:

SELECT REGEXP_REPLACE('+34 (947) 123 456 ext. 2013', '[^0-9]+', '')
FROM DUAL

This example returns 349471234562013.

Alternative syntaxes include:

POSIX character classes:
```
'[^[:digit:]]+'
```
Perl-influenced extensions (since Oracle 11):
```
'\D+'
```

edited Jan 16, 2014 at 16:00

answered Oct 19, 2010 at 12:23

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

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

4 Comments

Amir Syafrudin Over a year ago

This answer saved my day. Though through a bit of googling I found out that you can replace '[^0-9]+' with '[^[:digit:]]+'. :)

Álvaro González Over a year ago

@AmirSyafrudin - That's a POSIX character class. If I recall correctly, they weren't supported in earlier versions, but should work fine with recent releases.

Amir Syafrudin Over a year ago

Thanks for the heads up, Alvaro. I got it from here: orafaq.com/node/2404, tested it in my 11g database, and posted my comment above. I guess during the process I kinda forgot to notice which version this is supported. :)

Álvaro González Over a year ago

@AmirSyafrudin Please disregard the part about Oracle version. I was confused with PERL-Influenced Extensions such as \d. POSIX syntax was available from the beginning (i.e., 10g).

Tony Andrews · Accepted Answer · 2010-10-19 12:49:30Z

15

For older versions of Oracle that don't support regular expressions:

select translate (phone_no,'0'||translate (phone_no,'x0123456789','x'),'0')
from mytable;

The inner translate gets all the non-digit characters from the phone number, and the outer translate then removes them from the phone number.

answered Oct 19, 2010 at 12:49

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

1 Comment

Galbarad Over a year ago

really coll way! I suppose hat regexp take more CPU power instead of translate

Collectives™ on Stack Overflow

Oracle: Replacing non-numeric chars in a string

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related