Does somebody know a better way

I am trying to strip non-alphanumeric characters from a string - I have come up with a solution using the TRANSLATE function e.g.

select translate(' GH%TEst String @@~~##',

           '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz
{|}~ !"#$%&''()*+,-./:;<=>?@[\]^_`',

           '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz ') from dual;

However this seems cumbersome - is there a better way of doing this?

Also, the nature of my solution is to exclude specified characters. I would prefer a solution that just includes alphanumeric characters in the output, rather than excludes those characters I specify as nonalphanumeric.  This this would be a better algorithm IMO. In my current solution I have only specified ASCII 32 to 126 as the characters to exclude.

I am worrying about performance (this function will be run millions of times every day) so wanted to find the fastest way of meeting the requirement using an inbuilt SQL function if possible, rather than knocking up a piece of PL/SQL to do it. I could write the PL/SQL easily but prefer to avoid this if there is an inbuilt function to use instead.

Thanks in advance for any proposals,

Cheers,
Chris Received on Fri Oct 18 2002 - 05:49:02 CDT