Re: Strange whitespace hard to trim off

Mark D Powell wrote:
>
> C Chang <cschang_at_maxinter.net> wrote in message news:<3E1CF430.2E7D_at_maxinter.net>...
> > I have a record ( submitted by customer) as M123-1-9. When I count the
> > length of it, it has 9 characters. I use -
> > Select part , LENGTH(RTRIM(part)) p from ... to remove the whitespace
> > off. However I still have 9 after the process. I have tried various
> > chr() such as new-line, space, return character, none of them work. I
> > tested with SUBSTR(part, 8,2) ||'*" and confirmed it to be a whitespace
> > as 9 *. I just could not trim it off. Does anyone have a better
> > suggestion?
> > C Chang
>
> The rtrim function defaults to space (blank) as the character to be
> trimmed. Use the DUMP function to see what the trailing characters
> are and then use RTRIM (other possibilites trim, replace, and
> transform) to eliminate them. See the SQL manual chapter on single
> row functions for the full syntax.
>
> HTH -- Mark D Powell --

Thanks Mark. I never use the DUMP function and I will try. I have tried with the RTRIM function (LTRIM as well) but it won't trim off the whitespace, because the LENGTH(RTRIM(...)) gave the same lenght as before. By the way, I have Oracle 8.1.6 on NT 4. as example:

SELECT part, SUBSTR(part, 9,2) ||'*' c, LENGTH(PART) l, LENGTH(RTRIM(part) pl FROM ...

part           c        l        pl
----------   ----      ----     ----
MS123-1-9     9 *       10       10

C chang Received on Thu Jan 09 2003 - 21:56:34 CST