RE: Phantom characters

From: David Barbour <dbarbour_at_connectsouth.com>
Date: Wed, 17 Jan 2001 10:32:10 -0600
Message-Id: <10744.126895@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C080A3.09E76C80
Content-Type: text/plain;

charset="iso-8859-1"

Thanks Jacques - worked like a charm. Interestingly, whether it was an octal, decimal, hex or ascii dump, the referenced character still wasn't in the handy list I have from "The C Programmer's Handbook" from AT&T Bell Laboratories. Some of this data was loaded via sqlldr from flat files created by Excel spreadsheets and I wonder if Microsoft has their own set of unprintable characters!

Anyway, thanks to all that responded - there were 147,000+ rows affected, it's fixed, and the developers think I'm some sort of genius.

David A. Barbour
Oracle DBA - ConnectSouth
512-681-9438
dbarbour_at_connectsouth.com

-----Original Message-----
From: Jacques Kilchoer [mailto:Jacques.Kilchoer_at_quest.com] Sent: Tuesday, January 16, 2001 7:40 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Phantom characters

> -----Original Message-----
> From: David Barbour [ mailto:dbarbour_at_connectsouth.com
<mailto:dbarbour_at_connectsouth.com> ]
> Sent: mardi, 16. janvier 2001 16:45
>
> I've got some entries in one of my columns that appear to
> contain a leading
> space or spaces. When I try to use ltrim, they don't go
> away. If I spool
> the data, I find these spaces are actually represented in the
> spool file as
> /240. I don't have this number as an ascii, decimal or octal
> representation
> of anything, and am at a loss as to how to identify and remove these
> "phantoms".

Try "dump" to get the value to supply to the chr function (will probably be 240), then use the ltrim function.

e.g.

SQL> select dump ('xxxMy name') from dual ;

DUMP('XXXMYNAME')

Typ=96 Len=10: 120,120,120,77,121,32,110,97,109,101

SQL> select ltrim ('xxxMy Name', chr (120)) from dual ;

LTRIM('

My Name

any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely upon my employer.

Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com <http://www.quest.com>

------_=_NextPart_001_01C080A3.09E76C80
Content-Type: text/html;

charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>RE: Phantom characters</TITLE>

<META content="MSHTML 5.50.4522.1800" name=GENERATOR></HEAD>

<BODY>
<DIV>Thanks Jacques - worked like a charm.  Interestingly, whether it was an octal, decimal, hex or ascii dump, the referenced character still wasn't in the handy list I have from "The C Programmer's Handbook" from AT&T Bell Laboratories.  Some of this data was loaded via sqlldr from flat files created by Excel spreadsheets and I wonder if Microsoft has their own set of unprintable characters!  </DIV> <DIV> </DIV>
<DIV>Anyway, thanks to all that responded - there were 147,000+ rows affected, it's fixed, and the developers think I'm some sort of genius.</DIV>
<DIV> </DIV>
David A. Barbour <FONT face=Arial

size=2>Oracle DBA - ConnectSouth</FONT> <BR><FONT face=Arial 
size=2>512-681-9438</FONT> <BR><FONT face=Arial 
size=2>dbarbour_at_connectsouth.com</FONT> </P>

<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px"> <DIV class=OutlookMessageHeader dir=ltr align=left>-----Original Message----- From: Jacques Kilchoer [mailto:Jacques.Kilchoer_at_quest.com] Sent: Tuesday, January 16, 2001 7:40 PM To: Multiple recipients of list ORACLE-L Subject: RE: Phantom characters </DIV> > -----Original Message----- > From: David Barbour [<A
href="mailto:dbarbour_at_connectsouth.com">mailto:dbarbour_at_connectsouth.com</A>] > Sent: mardi, 16. janvier 2001 16:45 > > I've got some entries in one of my columns that appear to > contain a leading > space or spaces.  When I try to use ltrim, they don't go > away.  If I spool > the data, I find these spaces are actually represented in the > spool file as > /240.  I don't have this number as an ascii, decimal or octal > representation > of anything, and am at a loss as to how to identify and remove these > "phantoms". Try "dump" to get the value to supply to the chr function (will probably be 240), then use the ltrim function.

  <P><FONT size=2>e.g.</FONT> </P>
  <P><FONT size=2>SQL&gt; select dump ('xxxMy name') from dual ;</FONT> </P>
  <P><FONT size=2>DUMP('XXXMYNAME')</FONT> <BR><FONT 
  size=2>---------------------------------------------------</FONT> <BR><FONT

size=2>Typ=96 Len=10: 120,120,120,77,121,32,110,97,109,101 SQL> select ltrim ('xxxMy Name', chr (120)) from dual ; 
 LTRIM(' ------- My Name 
 ------ any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely

  upon my employer.</FONT></P>
  <P><FONT size=2></FONT> <BR><FONT size=2>Jacques R. Kilchoer</FONT> <BR><FONT 
  size=2>(949) 754-8816</FONT> <BR><FONT size=2>Quest Software, Inc.</FONT>

8001 Irvine Center Drive Irvine, California 92618 U.S.A. <A target=_blank href="http://www.quest.com">http://www.quest.com</A> Received on Wed Jan 17 2001 - 10:32:10 CST