Re: Rtrim seems not to work as expected - THANKS ALL

"Thomas T" <T_at_T> wrote:

>"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
>

THANK YOU ALL !!!  It was the concatenation I used when I had it working -

Just another case of what Shirley McClane calls CRAFT ( Can't Remember AI Freakin' Thing) Received on Fri Aug 15 2003 - 14:30:45 CDT