Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Need idea to strip tabs (chr(9) hex 9) and carriage return (c

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

From: Hagedorn, Linda <lindah_at_epocrates.com>
Date: Fri, 18 Jan 2002 15:50:10 -0800
Message-ID: <F001.003F48EA.20020118153648@fatcity.com> 







<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_at_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_at_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_at_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">
Received on Fri Jan 18 2002 - 17:50:10 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US