| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Need idea to strip tabs (chr(9) hex 9) and carriage return (c
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" <lindah_at_epocrates.com>
Sent by: root_at_fatcity.com
01/18/02 03:36 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
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
wcarle_at_att.com
-----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 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: Jared.Still_at_radisys.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).Received on Fri Jan 18 2002 - 20:01:58 CST
 |
 |