Then with this, put it in a package using UTL_RAW.TRANSLATE to reduce
overhead.
HTH
tED
"Jusung Yang" <JusungYang_at_yahoo.com> wrote in message
news:130ba93a.0210181058.2fa304_at_posting.google.com...
> You can try this :
>
> SQL> select
translate('++**987u^^BBw+%$00','1'||translate(lower('++**987u^^BBw+%$00'),'+
abcdefghijkl
> mnopqrstuvwxyz0123456789','+'),'1') from dual;
>
> TRANSLATE
> ---------
> 987uBBw00
>
> SQL> select
translate('12#12$ab%cd*','1'||translate(lower('12#12$ab%cd*'),'+abcdefghijkl
mnopqrstuvwx
> yz0123456789','+'),'1') from dual;
>
> TRANSLAT
> --------
> 1212abcd
>
>
> 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')
>
>
> - Jusung Yang
>
>
> 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 Sat Oct 19 2002 - 05:08:41 CDT
