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

From: Carle, William T (Bill), ALINF <wcarle_at_att.com>
Date: Fri, 18 Jan 2002 13:29:13 -0800
Message-ID: <F001.003F4459.20020118122524@fatcity.com>

0D) from fields

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>Linda,

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'> 

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span style="mso-spacerun: yes">    I just did something like this yesterday. You will need to use the TRANSLATE function. So you can use an SQL statement like:

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'> 

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>update <table name>

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>set fld1 = translate(fld1,chr(09),&#8217;-&#8216;);

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'> 

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><span style="mso-spacerun: yes">    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&#8217;t see them. You might want to use the DUMP function to look at what is really in the field. Good luck!

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'> 

<!---size:10.0pt;mso-bidi-font-size:

12.0pt;font-family:Arial'><span
style="mso-spacerun: yes"> AUTOTEXTLIST \s "E-mail Signature" <font
color="#339966" face=Impact><span style='font-family:Impact;color:#339966; font-style:italic'>

<span

style='font-size:12.0pt;font-family:Impact;color:#339966;font-style:italic'>Bill Carle

<span

style='font-size:12.0pt;font-family:"Century Gothic";color:navy'>AT&T<font color=navy face="Century Gothic"><span style='font-family:"Century Gothic"; color:navy;mso-color-alt:windowtext'>

<span

style='font-size:12.0pt;font-family:"Century Gothic";color:navy'>Database Administrator<span
style='font-family:"Century Gothic";color:navy;mso-color-alt:windowtext'>

<span

style='font-size:12.0pt;font-family:"Century Gothic";color:navy'>816-995-3922<font color=navy face="Century Gothic"><span style='font-family:"Century Gothic"; color:navy;mso-color-alt:windowtext'>

<span

style='font-size:12.0pt;font-family:"Century Gothic";color:navy'>wcarle_at_att.com<font color=navy face="Century Gothic"><span style='font-family:"Century Gothic"; color:navy;mso-color-alt:windowtext'>

<!---size:10.0pt;mso-bidi-font-size:

12.0pt;font-family:Arial'><span
class=EmailStyle16><span style='font-size: 10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'> 

<font size=2 color=black

face=Tahoma>-----Original
Message-----
From: Hagedorn, Linda
[mailto:lindah_at_epocrates.com]
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

<span

style='font-size:12.0pt'> 

<span

style='font-size:10.0pt;color:black'>Hi, <span style='color:black;mso-color-alt:windowtext'>

<span

style='font-size:10.0pt;color:black'>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.    <font color=black>

<span

style='font-size:10.0pt;color:black'>If anyone has had to do this, I'd appreciate knowing your method. <span
style='color:black;mso-color-alt:windowtext'>

<span

style='font-size:10.0pt;color:black'>Thanks, Linda    <font color=black>

<span

style='font-size:10.0pt;color:black'>Table             Column      
Contents             
Rawtohex
<span style='font-size:10.0pt;

color:black'>----------------- ------------ --------------------
---------------------------------<span

style='color:black'>
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Canada-Alberta        43616E6164612D416C62657274612009<font color=black>
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Canada-British Colum 43616E6164612D4272697469736820436F6C756D6269612009<font color=black>
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Canada-Manitoba      43616E6164612D4D616E69746F62612009<font color=black>
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Canada-New Foundland 43616E6164612D4E657720466F756E646C616E642009<span style='color:black'>
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Canada-Nova Scotia        43616E6164612D4E6F76612053636F7469612009<font color=black>
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Canada-Ontario        43616E6164612D4F6E746172696F2009<font color=black>
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Canada-Quebec          43616E6164612D5175656265632009<font color=black>
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Canada-Saskatchewan  43616E6164612D5361736B617463686577616E2009<span style='color:black'>
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Afghanistan             
41666768616E697374616E2009<span
style='color:black'>
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Albania             
416C62616E69612009
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Algeria             
416C67657269612009
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Algeria             
416C67657269612009
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Angola               
416E676F6C612009
<span style='font-size:10.0pt;

color:black'>REG.AMA_COUNTRIES COUNTRY_NAME Antigua             
416E74696775612009 <font
color=black>

<font size=3 color=black

face="Times New Roman"> <font
color=black>

<span

style='font-size:10.0pt;color:black'>Table       Column      Contents   Rawtohex<span
style='color:black'>
<span style='font-size:10.0pt;

color:black'>----------- ----------- ---------- ------------<font color=black>
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84708      38343730380D
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84708      38343730380D
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84709      38343730390D
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84709      38343730390D
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84710      38343731300D
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84710      38343731300D
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84710      38343731300D
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84710      38343731300D
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84710      38343731300D<font color=black>
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84710      38343731300D
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84710      38343731300D
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84710      38343731300D
<span style='font-size:10.0pt;

color:black'>REG.AMA_LOG MEDSCHOOLID 84710      38343731300D <font
color=black> Received on Fri Jan 18 2002 - 15:29:13 CST