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:
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?
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+'