Regular expressions to the rescue!
Here's a test case in case you want to try it.
Jared
------------------------
drop table regex;
create table regex (
test varchar2(20)
);
create or replace function strip_str ( data_in varchar2 )
return varchar2
is
test_str varchar2(4000);
begin
test_str := data_in;
owa_pattern.change(test_str, '\x0a', '', 'g');
owa_pattern.change(test_str, '\x0c', '', 'g');
owa_pattern.change(test_str, '\x0d', '', 'g');
return test_str;
end;
/
show error function strip_str
insert into regex values( 'carriage' || chr(13) || 'return');
insert into regex values( 'line' || chr(10) || 'feeds' || chr(10));
insert into regex values( 'form feed' || chr(12));
commit;
select test
from regex;
select strip_str(test) test
from regex
/
"Hagedorn, Linda" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/18/02 03:36 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: Need idea to strip tabs (chr(9) hex 9) and carriage return
(c
I couldn't get translate to just strip off the chr(13), but was able to
using this:
update ama_log set medschoolid = trim(trailing (chr(13)) from medschoolid
) where medschoolid like '95701%' ;
update ama_log set medschoolid = trim(trailing (chr(13)) from medschoolid
) where medschoolid like '95702%' ;
Before:
'REG.AMA_LOGMEDSCHOOLID TRIM(TRAIL SUBSTR(RAWTOHEX("MED
----------------------- ---------- --------------------
REG.AMA_LOG MEDSCHOOLID 95701 39353730310D
REG.AMA_LOG MEDSCHOOLID 95701 39353730310D
REG.AMA_LOG MEDSCHOOLID 95702 39353730320D
REG.AMA_LOG MEDSCHOOLID 95704 39353730340D
After:
'REG.AMA_LOGMEDSCHOOLID TRIM(TRAIL SUBSTR(RAWTOHEX("MED
----------------------- ---------- --------------------
REG.AMA_LOG MEDSCHOOLID 95701 3935373031
REG.AMA_LOG MEDSCHOOLID 95701 3935373031
REG.AMA_LOG MEDSCHOOLID 95702 3935373032
REG.AMA_LOG MEDSCHOOLID 95704 39353730340D
-----Original Message-----
Sent: Friday, January 18, 2002 12:25 PM
To: Multiple recipients of list ORACLE-L
r(1
Linda,
I just did something like this yesterday. You will need to use the
TRANSLATE function. So you can use an SQL statement like:
update <table name>
set fld1 = translate(fld1,chr(09),'-');
Make sure you only have one weird character in the field though. I
actually and a carriage return and a new line back to back and, of course,
you can't see them. You might want to use the DUMP function to look at
what is really in the field. Good luck!
Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]
-----Original Message-----
Sent: Friday, January 18, 2002 1:57 PM
To: Multiple recipients of list ORACLE-L
Hi,
Sometime in the past, data was loaded into tables from spreadsheets and
the tabs and form feeds were included in the data. I can locate all the
bad data, and am looking for a clear method to remove only the 'bad'
character from a field, despite where it occurs. For example, you can see
09 at the end of the rawtohex column. I need to change
Canada-Albertachr(9) to 'Canada-Alberta'. The form feeds in the second
example are in a numeric field.
If anyone has had to do this, I'd appreciate knowing your method.
Thanks, Linda
Table Column Contents Rawtohex
----------------- ------------ --------------------
---------------------------------
REG.AMA_COUNTRIES COUNTRY_NAME Canada-Alberta
43616E6164612D416C62657274612009
REG.AMA_COUNTRIES COUNTRY_NAME Canada-British Colum
43616E6164612D4272697469736820436F6C756D6269612009
REG.AMA_COUNTRIES COUNTRY_NAME Canada-Manitoba
43616E6164612D4D616E69746F62612009
REG.AMA_COUNTRIES COUNTRY_NAME Canada-New Foundland
43616E6164612D4E657720466F756E646C616E642009
REG.AMA_COUNTRIES COUNTRY_NAME Canada-Nova Scotia
43616E6164612D4E6F76612053636F7469612009
REG.AMA_COUNTRIES COUNTRY_NAME Canada-Ontario
43616E6164612D4F6E746172696F2009
REG.AMA_COUNTRIES COUNTRY_NAME Canada-Quebec
43616E6164612D5175656265632009
REG.AMA_COUNTRIES COUNTRY_NAME Canada-Saskatchewan
43616E6164612D5361736B617463686577616E2009
REG.AMA_COUNTRIES COUNTRY_NAME Afghanistan 41666768616E697374616E2009
REG.AMA_COUNTRIES COUNTRY_NAME Albania 416C62616E69612009
REG.AMA_COUNTRIES COUNTRY_NAME Algeria 416C67657269612009
REG.AMA_COUNTRIES COUNTRY_NAME Algeria 416C67657269612009
REG.AMA_COUNTRIES COUNTRY_NAME Angola 416E676F6C612009
REG.AMA_COUNTRIES COUNTRY_NAME Antigua 416E74696775612009
Table Column Contents Rawtohex
----------- ----------- ---------- ------------
REG.AMA_LOG MEDSCHOOLID 84708 38343730380D
REG.AMA_LOG MEDSCHOOLID 84708 38343730380D
REG.AMA_LOG MEDSCHOOLID 84709 38343730390D
REG.AMA_LOG MEDSCHOOLID 84709 38343730390D
REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
REG.AMA_LOG MEDSCHOOLID 84710 38343731300D
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
