Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL to remove char from string?
> TRANSLATE(PHONE,'*'||TRANSLATE(PHONE,'*0123456789','*'),'*')
>
> The character * is a dummy-character. TRANSLATE must have at least
> one replacement character, so you replace * with * and the rest with
> NULL.
I must correct myself. In this example the * would not removed
from the string. If you want blank-characters to be in the string,
use blank as dummy-character.
Alternatively, you can remove the dummy-character with:
REPLACE(PHONE,<dummy-character>)
All together looks like this:
REPLACE(TRANSLATE(PHONE,'*'||TRANSLATE(PHONE,'*0123456789','*'),'*'),'*')
Angelo.
Received on Tue Jan 20 1998 - 00:00:00 CST