Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Does somebody know a better way
You can try this :
SQL> select translate('++**987u^^BBw+%$00','1'||translate(lower('++**987u^^BBw+%$00'),'+abcdefghijkl mnopqrstuvwxyz0123456789','+'),'1') from dual;
TRANSLATE
SQL> select translate('12#12$ab%cd*','1'||translate(lower('12#12$ab%cd*'),'+abcdefghijklmnopqrstuvwx yz0123456789','+'),'1') from dual;
TRANSLAT
The logic is like this:
1. Find all non-alphanumeric characters in the string with
translate(<string>,'+abcdefghijklmnopqrstuvwxyz0123456789','+')
2. Remove these characters with the outer TRANSLATE. translate(<string>,'1<trash>','1')
Das Schaf <me_at_domain.com> wrote in message news:<MPG.1819f76f3cf348db989680_at_news.compaq.com>...
> 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
Received on Fri Oct 18 2002 - 13:58:00 CDT