From lindah@epocrates.com Fri, 18 Jan 2002 15:45:44 -0800
From: "Hagedorn, Linda" <lindah@epocrates.com>
Date: Fri, 18 Jan 2002 15:45:44 -0800
Subject: RE: Need idea to strip tabs (chr(9) hex 9) and carriage return (c
Message-ID: <F001.003F4880.20020118145534@fatcity.com>
MIME-Version: 1.0
Content-Type: text/plain
Title: Need idea to strip tabs (chr(9) hex 9) and carriage return (chr(13) hex 0D) from fields



<FONT face="Courier New" color=#0000ff 
size=2>Thanks very much Bill.    
<FONT face="Courier New" color=#0000ff 
size=2> 
<SPAN 
class=769335322-18012002>Regards, Linda <SPAN 
class=769335322-18012002> <SPAN 
class=769335322-18012002>
<FONT face="Courier New" color=#0000ff 
size=2> 

  <FONT face="Times New Roman" 
  size=2>-----Original Message-----From: Carle, William T (Bill), 
  ALINF [mailto:wcarle@att.com]Sent: Friday, January 18, 2002 12:25 
  PMTo: Multiple recipients of list ORACLE-LSubject: RE: 
  Need idea to strip tabs (chr(9) hex 9) and carriage return 
  (chr(1
  
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">Linda,
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"> 
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"><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:
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"> 
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">update 
  <table name>
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt">set 
  fld1 = translate(fld1,chr(09),&#8217;-&#8216;);
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"> 
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"><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!
  <FONT face=Arial color=navy 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"> 
  <!---size:10.0pt;mso-bidi-font-size:
12.0pt;font-family:Arial'><span 
style="mso-spacerun: yes"> AUTOTEXTLIST \s "E-mail 
Signature" <FONT 
  face=Impact color=#339966><SPAN 
  style="COLOR: #339966; FONT-STYLE: italic; FONT-FAMILY: Impact">
  <SPAN 
  style="FONT-SIZE: 12pt; COLOR: #339966; FONT-STYLE: italic; FONT-FAMILY: Impact">Bill 
  Carle
  <SPAN 
  style="FONT-SIZE: 12pt; COLOR: navy; FONT-FAMILY: 'Century Gothic'">AT&T<FONT 
  face="Century Gothic" color=navy><SPAN 
  style="COLOR: navy; FONT-FAMILY: 'Century Gothic'; mso-color-alt: windowtext">
  <SPAN 
  style="FONT-SIZE: 12pt; COLOR: navy; FONT-FAMILY: 'Century Gothic'">Database 
  Administrator<SPAN 
  style="COLOR: navy; FONT-FAMILY: 'Century Gothic'; mso-color-alt: windowtext">
  <SPAN 
  style="FONT-SIZE: 12pt; COLOR: navy; FONT-FAMILY: 'Century Gothic'">816-995-3922<FONT 
  face="Century Gothic" color=navy><SPAN 
  style="COLOR: navy; FONT-FAMILY: 'Century Gothic'; mso-color-alt: windowtext">
  <SPAN 
  style="FONT-SIZE: 12pt; COLOR: navy; FONT-FAMILY: 'Century Gothic'">wcarle@att.com<FONT 
  face="Century Gothic" color=navy><SPAN 
  style="COLOR: navy; FONT-FAMILY: 'Century Gothic'; mso-color-alt: windowtext">
  <!---size:10.0pt;mso-bidi-font-size:
12.0pt;font-family:Arial'><SPAN 
  class=EmailStyle16><SPAN 
  style="FONT-SIZE: 10pt; FONT-FAMILY: Arial; mso-bidi-font-size: 12.0pt"> 
  <FONT face=Tahoma color=black 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Tahoma">-----Original 
  Message-----From: Hagedorn, 
  Linda [mailto:lindah@epocrates.com]<SPAN 
  style="FONT-WEIGHT: bold">Sent: Friday, January 18, 2002 1:57 
  PMTo: Multiple recipients of 
  list ORACLE-LSubject: Need 
  idea to strip tabs (chr(9) hex 9) and carriage return (chr(1
  <FONT face="Times New Roman" 
  size=3><SPAN 
  style="FONT-SIZE: 12pt"> 
  <FONT face="Times New Roman" color=black 
  size=2>Hi, <FONT 
  color=black><SPAN 
  style="COLOR: black; mso-color-alt: windowtext">
  <FONT face="Times New Roman" color=black 
  size=2>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.    
  <SPAN 
  style="COLOR: black; mso-color-alt: windowtext">
  <FONT face="Times New Roman" color=black 
  size=2>If anyone has had to do 
  this, I'd appreciate knowing your method. <FONT 
  color=black><SPAN 
  style="COLOR: black; mso-color-alt: windowtext">
  <FONT face="Times New Roman" color=black 
  size=2>Thanks, 
  Linda    <SPAN 
  style="COLOR: black; mso-color-alt: windowtext">
  <FONT face="Times New Roman" color=black 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">Table             
  Column       
  Contents              
  Rawtohex <SPAN 
  style="COLOR: black"><SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">----------------- ------------ 
  -------------------- ---------------------------------<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_COUNTRIES 
  COUNTRY_NAME Canada-Alberta        
  43616E6164612D416C62657274612009<SPAN 
  style="COLOR: black"> <SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">REG.AMA_COUNTRIES COUNTRY_NAME 
  Canada-British Colum 
  43616E6164612D4272697469736820436F6C756D6269612009<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_COUNTRIES 
  COUNTRY_NAME Canada-Manitoba      
  43616E6164612D4D616E69746F62612009<SPAN 
  style="COLOR: black"> <SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">REG.AMA_COUNTRIES COUNTRY_NAME 
  Canada-New Foundland 
  43616E6164612D4E657720466F756E646C616E642009<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_COUNTRIES 
  COUNTRY_NAME Canada-Nova Scotia        
  43616E6164612D4E6F76612053636F7469612009<SPAN 
  style="COLOR: black"> <SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">REG.AMA_COUNTRIES COUNTRY_NAME 
  Canada-Ontario        
  43616E6164612D4F6E746172696F2009<SPAN 
  style="COLOR: black"> <SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">REG.AMA_COUNTRIES COUNTRY_NAME 
  Canada-Quebec          
  43616E6164612D5175656265632009<SPAN 
  style="COLOR: black"> <SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">REG.AMA_COUNTRIES COUNTRY_NAME 
  Canada-Saskatchewan  
  43616E6164612D5361736B617463686577616E2009<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_COUNTRIES 
  COUNTRY_NAME Afghanistan      
          
  41666768616E697374616E2009<SPAN 
  style="COLOR: black"> <SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">REG.AMA_COUNTRIES COUNTRY_NAME 
  Albania  
              
  416C62616E69612009 
  <SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">REG.AMA_COUNTRIES COUNTRY_NAME 
  Algeria  
              
  416C67657269612009 
  <SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">REG.AMA_COUNTRIES COUNTRY_NAME 
  Algeria  
              
  416C67657269612009 
  <SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">REG.AMA_COUNTRIES COUNTRY_NAME Angola 
    
               
  416E676F6C612009 
  <SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">REG.AMA_COUNTRIES COUNTRY_NAME 
  Antigua  
              
  416E74696775612009 
  <SPAN 
  style="COLOR: black; mso-color-alt: windowtext">
  <FONT face="Times New Roman" 
  color=black size=3> <FONT 
  color=black><SPAN 
  style="COLOR: black; mso-color-alt: windowtext">
  <FONT face="Times New Roman" color=black 
  size=2><SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">Table       
  Column      Contents   
  Rawtohex 
  <SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">----------- ----------- ---------- 
  ------------ 
  <SPAN 
  style="FONT-SIZE: 10pt; COLOR: black">REG.AMA_LOG MEDSCHOOLID 
  84708      38343730380D<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_LOG MEDSCHOOLID 
  84708      38343730380D<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_LOG MEDSCHOOLID 
  84709      38343730390D<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_LOG MEDSCHOOLID 
  84709      38343730390D<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_LOG MEDSCHOOLID 
  84710      38343731300D<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_LOG MEDSCHOOLID 
  84710      38343731300D<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_LOG MEDSCHOOLID 
  84710      38343731300D<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_LOG MEDSCHOOLID 
  84710      38343731300D<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_LOG MEDSCHOOLID 
  84710      38343731300D<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_LOG MEDSCHOOLID 
  84710      38343731300D<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_LOG MEDSCHOOLID 
  84710      38343731300D<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_LOG MEDSCHOOLID 
  84710      38343731300D<FONT 
  color=black> <FONT color=black 
  size=2>REG.AMA_LOG MEDSCHOOLID 
  84710      38343731300D<FONT 
  color=black> <SPAN 
  style="COLOR: black; mso-color-alt: windowtext">