Re: How can I convert strings efficiently?

From: SoulSurvivor <markyg_7_at_yahoo.co.uk>
Date: 2 Apr 2003 06:37:02 -0800
Message-ID: <8d9c6fd.0304020637.564d1daf@posting.google.com>

You can write your own function to loop through the string and replace any junk if you wish if you dont want to use Replace.

Here is one i just done.

---

CREATE OR REPLACE FUNCTION Format_String(in_orig_string IN VARCHAR2) RETURN VARCHAR2 IS

   v_new_string		VARCHAR2(1000);
   v_length	NUMBER;
   V_chr	varchar2(1);
   V_include	BOOLEAN;

BEGIN
   v_length := LENGTH(in_orig_string);

   FOR i IN 1..v_length LOOP

      v_include := FALSE;
      v_chr := SUBSTR(in_orig_string, i, 1);

      IF (ASCII(v_chr) >= 65 AND ASCII(v_chr) <= 90) OR  	-- A to Z
         (ASCII(v_chr) >= 97 AND ASCII(v_chr) <= 122) OR 	-- a to z
         (ASCII(v_chr) >= 48 AND ASCII(v_chr) <= 57) THEN 	-- 0 to 9

	 v_include := TRUE;
      END IF;

      IF v_include THEN
         v_new_string := v_new_string || v_chr;
      END IF;

   END LOOP;    RETURN v_new_string;

END;   1* select Format_String('ABC;[=/#DEF12+3') FROM DUAL SQL> / FORMAT_STRING('ABC;[=/#DEF12+3')

---

ABCDEF123 "Guido Konsolke" <Guido.Konsolke_at_triaton.com> wrote in message news:<1049281908.139138_at_news.thyssen.com>...
> 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
Received on Wed Apr 02 2003 - 08:37:02 CST