Message-Id: <10744.126895@fatcity.com> From: David Barbour Date: Wed, 17 Jan 2001 10:32:10 -0600 Subject: RE: Phantom characters 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@connectsouth.com -----Original Message----- From: Jacques Kilchoer [mailto:Jacques.Kilchoer@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@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 ------_=_NextPart_001_01C080A3.09E76C80 Content-Type: text/html; charset="iso-8859-1" RE: Phantom characters
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@connectsouth.com

-----Original Message-----
From: Jacques Kilchoer [mailto:Jacques.Kilchoer@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@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