Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Does somebody know a better way

Re: Does somebody know a better way

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 18 Oct 2002 11:58:00 -0700
Message-ID: <130ba93a.0210181058.2fa304@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*'),'+abcdefghijklmnopqrstuvwx 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')

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US