Using Translate

I had this tip published before on : http://www.dbaoncall.net/references/tt_translate.html

But just in case one day that site is no longer available, I thought I better keep a copy in my own blog too:

If you have a varchar2 field and you want only the numerics, or perhaps some extra escape characters have found their way in, you can strip the field just to numerics by performing a translate such as below:
select translate('abc123'||chr(10)||'F',
'0'||translate('abc123'||chr(10)||'F', 'A1234567890', 'A'), '0')
from ...

This takes all the numbers out of the string and uses the resulting string as the translate so that the result is then only the numbers. Needless to say that one can also clean up the database using:

update tableA
set field = translate(field,'0'||translate(field,'A1234567890','A'),'0')