Removing multiple characters from a string with PL/SQL

[slicendice]

Hi
I have a string in a PL/SQL program that may contain various characters that are unwanted (for example commas, full stops, hyphens, etc) which I need to strip out. I know the REPLACE function can be used to get rid of single characters (e.g. REPLACE('ab-cd', '-') will give just 'abcd') - what I'm wondering is, is it possible to remove several different characters from a string in one go? Or am I going to have to have multiple calls to REPLACE to remove each individual unwanted character?

Thanks very much

 

[SantaMufasa]

Slice,

Yes, you can use the TRANSLATE function:

SQL> select translate ('abc,def.ghi-jkl','x,.-','x') from dual;

TRANSLATE('A
------------
abcdefghijkl

The function requires three arguments: 1) object string 2) search-for-character string, and 3) replacement-character string. If the replacement-character string is shorter than the search-for-character string, then Oracle replaces "found" characters with NULLs.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:09 (07Jul04) UTC (aka "GMT" and "Zulu"), 10:09 (07Jul04) Mountain Time)

 

[jaggiebunnet]

You could try something like

select replace(translate('abcd%dfg,14;', '%,;', 'X'), 'X') from dual

 

[slicendice]

Jaggie, Santa
Thanks very much for your help - thats exactly what I was after! I was sure there'd be a function somewhere to do it, I just couldn't find the darned thing!

Thanks again...