Re: Does somebody know a better way

Das Schaf wrote:

> 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 non-
> alphanumeric. 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

One solution would be as follows:

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

You don't need the other stuff for TRANSLATE to do what you want.

Daniel Morgan Received on Fri Oct 18 2002 - 12:20:53 CDT