Re: Rtrim seems not to work as expected

TurkBear wrote:

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

The characters are not spaces. You must figure out what they are and then you can TRIM them.

Do this:

SELECT ASCII(SUBSTR(field,1,1)) FROM table_name;

Some will be zeros, some commas, the rest will tell you what is embedded. That is what you must trim.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)