Re: Rtrim seems not to work as expected

From: Thomas T <T_at_T>
Date: Fri, 15 Aug 2003 14:24:13 -0400
Message-ID: <3f3d254e$1@rutgers.edu>

"TurkBear" <john.greco_at_dot.state.mn.us> wrote in message news:dn4qjvo9k72nnraq59sgo0b87aoun1ectm_at_4ax.com...
> Daniel Morgan <damorgan_at_exxesolutions.com> wrote:
>
> >TurkBear wrote:
> >
> >> Details:
> >> Oracle 8.1.7.4
> >> SqlPlus 9.2
> >> W2K Advanced Server
> >>
> >> I am confused ( not a rare thing) , because I thought I had done this
kind of thing before:
> >> Create a CSV spool file from the database..
> >>
> >> Set colsep to ','
> >> set heading, feedback, etc off
> >> got the following:( employee numbers deleted for security - names are
public data.
>
>> -------------------------------------------------------------------------

---

> >> select rtrim(empl_nm),empl_nbr from hr_public where rownum < 9;
> >>
> >> Lambert,Ronald Paul ,00000000
> >> Hopkins,Ruth Eleanor ,00000000
> >> Coulianos,Theodore T ,00000000
> >> Clemen,Jacqueline Barbara ,00000000
> >> Martinez Jr,Bartolo ,00000000
> >> Conocchioli,Victor Renald ,00000000
> >> Church,Philip C ,00000000
> >> Crabbe,Victor Emmanuel Kweimensah ,00000000
> >>
>
>> -------------------------------------------------------------------------

---

> >>
> >> Why is it still allocating 50 spaces ( the field is a varchar2(50) )
even though I specified RTRIM?
> >>
> >> What am I missing?
> >>
> >> Thanks,
> >>
> >
> >Can't say without seeing the data. But the first thing I'd do is stop
using RTRIM and use TRIM.
>
> Thanks Daniel, tried that it makes no difference:
> [..snip..]

Here's my attempt at it. How long is the longest name?

I believe Oracle is making the column as long as the longest name. You're asking the select statement to give you individual columns. And that means Oracle is going to line those columns up for you.

I ran the following query on my database, and it behaved similarly to your example. The names at the end were 4 characters shy of the length of the column; the column turned out to be 32 characters long. What's strange is, I specified no formatting on that column. The last_name is 15 long, first_name is 15 long, and if you add the comma in, that turns out to be 31- not 32.

select rtrim(last_name || ',' || first_name) as bleh, id1 from abigtable
order by length(rtrim(last_name || ',' || first_name)) /

Try using this instead:

select rtrim(empl_nm) || ',' || empl_nbr from hr_public;

That will count everything as one column. You could even say

select rtrim(empl_nm) || ',' || empl_nbr as mybigcolumn from hr_public;

Hope that helps,

-Thomas Received on Fri Aug 15 2003 - 13:24:13 CDT