RE: PL/SQL : how to drop all the white characters from a string

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 05 Sep 2001 05:56:03 -0700
Message-ID: <F001.003831E2.20010905052634@fatcity.com>

Andrey,

how about the function below. you can change the SUBSTR commands below to tailor to your use.

To use the function, you can :

update {table}
set column_name = wtw_name_fix(column_name);

hope this helps.

CREATE OR REPLACE FUNCTION WTWDBA.Wtw_Name_Fix(in_string IN VARCHAR2)

RETURN VARCHAR2 IS
/*

Function Name : WTW_NAME_FIX

   Author        : Thomas Mercadante
                   Mercadante Systems Design
   Date          : 1/19/2001
   Purpose       : This function will accept a string of any length and
                   will return the same string, removing any character other
                                   than A thru Z or 0 thru 9 in upper case

format.

Modification History:
4/25/2001 T. Mercadante Allowed 0 thru 9 as valid characters. */

  in_length NUMBER;
  indx NUMBER;
  out_string VARCHAR2(2000);

BEGIN

   indx       := 1;
   in_length  := LENGTH(in_string);
   out_string := '';

   WHILE indx <= in_length LOOP
     IF UPPER(SUBSTR(in_string,indx,1)) BETWEEN 'A' AND 'Z'
         OR       SUBSTR(in_string,indx,1)  BETWEEN '0' AND '9' THEN
            out_string := out_string || UPPER(SUBSTR(in_string,indx,1));
         END IF;
     indx := indx + 1;

END LOOP; RETURN out_string;

END;
/

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Wednesday, September 05, 2001 8:05 AM To: Multiple recipients of list ORACLE-L

Dear all !

I need a PL/SQL proc that will drop all the white characters (spaces , tabs , enters etc.. ) from a string stored in a VARCHAR column I'm not trying to load my work to others , but ... if U have such a proc ready , would U please share .
Thanks a lot in advance.

DBAndrey

03-9254520
053-464562
mailto:andreyb_at_elrontelesoft.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Andrey Bronfin
INET: andreyb_at_elrontelesoft.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------