From lindah@epocrates.com Fri, 18 Jan 2002 15:50:10 -0800
From: "Hagedorn, Linda" <lindah@epocrates.com>
Date: Fri, 18 Jan 2002 15:50:10 -0800
Subject: RE: Need idea to strip tabs (chr(9) hex 9) and carriage return (c
Message-ID: <F001.003F48EA.20020118153648@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>I couldn't get translate to just strip off the chr(13), but was able to 
using this: 
<FONT face="Courier New" color=#0000ff 
size=2> 
<FONT face="Courier New" color=#0000ff 
size=2>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%' ; 

<FONT face="Courier New" color=#0000ff 
size=2> 
<FONT face="Courier New" color=#0000ff 
size=2> 
<FONT face="Courier New" color=#0000ff 
size=2>Before: 
<SPAN 
class=48393323-18012002><SPAN 
class=48393323-18012002> 
'REG.AMA_LOGMEDSCHOOLID 
TRIM(TRAIL SUBSTR(RAWTOHEX("MED----------------------- ---------- 
--------------------REG.AMA_LOG MEDSCHOOLID 
95701      3935373031<SPAN 
class=48393323-18012002>0DREG.AMA_LOG MEDSCHOOLID 
95701      3935373031<SPAN 
class=48393323-18012002>0DREG.AMA_LOG MEDSCHOOLID 
95702      3935373032<SPAN 
class=48393323-18012002>0DREG.AMA_LOG MEDSCHOOLID 
95704      39353730340D 
<SPAN 
class=48393323-18012002> 
<FONT face="Courier New" color=#0000ff 
size=2> 
<FONT face="Courier New" color=#0000ff 
size=2>After: 
'REG.AMA_LOGMEDSCHOOLID 
TRIM(TRAIL SUBSTR(RAWTOHEX("MED----------------------- ---------- 
--------------------REG.AMA_LOG MEDSCHOOLID 
95701      3935373031REG.AMA_LOG MEDSCHOOLID 
95701      3935373031REG.AMA_LOG MEDSCHOOLID 
95702      3935373032REG.AMA_LOG MEDSCHOOLID 
95704      39353730340D
 
<FONT face="Courier New" color=#0000ff 
size=2> 
<FONT face="Courier New" color=#0000ff 
size=2> 
<FONT face="Courier New" color=#0000ff 
size=2> 
<FONT face="Courier New" color=#0000ff 
size=2> 
<FONT face="Courier New" color=#0000ff 
size=2> 
<FONT face="Courier New" color=#0000ff 
size=2> 
<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">