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:45:44 -0800
Message-ID: <F001.003F4880.20020118145534@fatcity.com>

<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_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:45:44 CST

Original text of this message

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