RE: Need idea to strip tabs (chr(9) hex 9) and carriage return (c

[Hagedorn, Linda]

Title: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(13) hex 0D) from fields

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----- From: Carle, William T (Bill), ALINF [mailto:[EMAIL PROTECTED]] Sent: Friday, January 18, 2002 12:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(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----- From: Hagedorn, Linda [mailto:[EMAIL PROTECTED]] Sent: Friday, January 18, 2002 1:57 PM To: Multiple recipients of list ORACLE-L Subject: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(1   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