| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Need idea to strip tabs (chr(9) hex 9) and carriage return (c
<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="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),’-‘);
<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’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">----------------- ------------ -------------------- ---------------------------------<FONTcolor=black> <FONT color=black
color=black size=3> <FONT color=black><SPAN style="COLOR: black; mso-color-alt: windowtext">
 |
 |