Re: How can I convert strings efficiently?

From: andrewst <member14183_at_dbforums.com>
Date: Wed, 02 Apr 2003 12:25:19 +0000
Message-ID: <2719522.1049286319@dbforums.com>

Originally posted by Guido Konsolke
> Hello everyone,
>
> current environment: 8.1.7.0.0 Enterprise Edition.
>
> I've a little problem I can't solve nicely. I want
> to replace everything in a string with NULL
> that isn't a letter or a number.
>
> I try to avoid writing something like
> replace(replace(replace...))).
>
> I know there must be a better way, but can't
> figure it out since I don't code that much nowadays (or am to dumb).
> A quick search on google and asktom didn't satisfy me.
>
> If a nice person could give me a hint I would be grateful.
>
> Greetings,
> Guido

This double TRANSLATE will do it:

SQL> select translate( example,

  2                    'a'||translate( lower(example),
  3                                    '#abcdefghijklmnopqrstuvwxyz0123456789',
  4                                    '#'),
  5                    'a'
  6                  )

  7 from
  8 ( Select 'abcABC,.123$%^efg789*&*###' example from dual   9* );

TRANSLATE(EXAMP

---

abcABC123efg789

Probably requires some explanation:

1. The inner translate finds all the non-alphanumeric characters:

SQL> select translate( lower(example),

  2                    '#abcdefghijklmnopqrstuvwxyz0123456789',
  3                    '#')

  4 from
  5 ( Select 'abcABC,.123$%^efg789*&*###' example from dual   6* );

TRANSLATE(L

---

,.$%^*&*###

(The redundant translation of '#' to '#' is to prevent the 3rd argument from being NULL, which cocks everything up).

2) The outer translate takes the original string and translates any of

   the non-alpha characters from step 1 into NULLs, i.e. removes them.

--
Posted via http://dbforums.com

Received on Wed Apr 02 2003 - 06:25:19 CST