Need Help to Clean Up Phone Numbers

[jschodde]

I have a database with a phone number field that can have any combination of digits and characters in it. Some people have even type in names, notes, and other junk.

I don't care what they wrote, I just need to remove all the characters EXCEPT digits '0123456789'.

Can anyone help me on this? I need it to be part of a select statement and not a stored procedure, etc.

Thanks,
Jeff

 

[taupirho]

Try doing a double translate on your input string, something like :-


select translate('XYZ0123ABC','0'||translate('XYZ0123ABC','*0123456789','*'),'0)
from dual


=> 0123

 

[jschodde]

Good idea! I'll try that.
Thanks,
Jeff