30

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?

Hash
  • 4,647
  • 5
  • 21
  • 39
chris
  • 36,094
  • 53
  • 157
  • 237

2 Answers2

64

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+'
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • This answer saved my day. Though through a bit of googling I found out that you can replace `'[^0-9]+'` with `'[^[:digit:]]+'`. :) – Amir Syafrudin Jan 03 '13 at 07:49
  • @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. – Álvaro González Jan 03 '13 at 08:07
  • Thanks for the heads up, Alvaro. I got it from here: http://www.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. :) – Amir Syafrudin Jan 03 '13 at 09:22
  • @AmirSyafrudin Please disregard the part about Oracle version. I was confused with [PERL-Influenced Extensions](http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_regexp.htm#CHDDGICJ) such as `\d`. POSIX syntax was available from the beginning (i.e., 10g). – Álvaro González Jan 03 '13 at 09:29
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.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259