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:[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Andrey Bronfin
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).