Re: Rtrim seems not to work as expected

From: TurkBear <john.greco_at_dot.state.mn.us>
Date: Fri, 15 Aug 2003 12:13:55 -0500
Message-ID: <dn4qjvo9k72nnraq59sgo0b87aoun1ectm@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:

---

SQL> select trim(empl_nm),'000000' from hr_public where rownum < 15;

Lambert,Ronald Paul                                000000
Hopkins,Ruth Eleanor                               000000
Coulianos,Theodore T                               000000
Clemen,Jacqueline Barbara                          000000
Martinez Jr,Bartolo                                000000
Conocchioli,Victor Renald                          000000
Church,Philip C                                    000000
Crabbe,Victor Emmanuel Kweimensah                  000000
Dumas,Lawrence                                     000000
Williams,David Joseph                              000000
Wilkins,Craig Gordon                               000000
Terhaar,Stasia B                                   000000
Adiarte,Rosario Guerrero                           000000
Fenstermaker,Ricky Eugene                          000000

SQL> set colsep ','
SQL> /

Lambert,Ronald Paul                               ,000000
Hopkins,Ruth Eleanor                              ,000000
Coulianos,Theodore T                              ,000000
Clemen,Jacqueline Barbara                         ,000000
Martinez Jr,Bartolo                               ,000000
Conocchioli,Victor Renald                         ,000000
Church,Philip C                                   ,000000
Crabbe,Victor Emmanuel Kweimensah                 ,000000
Dumas,Lawrence                                    ,000000
Williams,David Joseph                             ,000000
Wilkins,Craig Gordon                              ,000000
Terhaar,Stasia B                                  ,000000
Adiarte,Rosario Guerrero                          ,000000
Fenstermaker,Ricky Eugene                         ,000000

SQL>

---

Without using any TRIM or RTRIM I get:

---

  1* select empl_nm,'000000' from hr_public where rownum < 15 SQL> /

Lambert,Ronald Paul                               ,000000
Hopkins,Ruth Eleanor                              ,000000
Coulianos,Theodore T                              ,000000
Clemen,Jacqueline Barbara                         ,000000
Martinez Jr,Bartolo                               ,000000
Conocchioli,Victor Renald                         ,000000
Church,Philip C                                   ,000000
Crabbe,Victor Emmanuel Kweimensah                 ,000000
Dumas,Lawrence                                    ,000000
Williams,David Joseph                             ,000000
Wilkins,Craig Gordon                              ,000000
Terhaar,Stasia B                                  ,000000
Adiarte,Rosario Guerrero                          ,000000
Fenstermaker,Ricky Eugene                         ,000000

SQL>

---

Data is

 EMPL_NM                                   NOT NULL VARCHAR2(50) 

I load it nightly to our warehouse from a statewide warehouse table( actually a view) , but even accessing it from the source makes no difference.

Very confused... Received on Fri Aug 15 2003 - 12:13:55 CDT