field with extra spaces

There is a field with extra spaces at the end. I wanted to trim the spaces to right before displaying query output. rtrim won't work.

I also tried substr(fieldname,1,length(fieldname)). It still displays some spaces. But, substr(fieldname,1,30) displays 30 chars only. Any ideas?

Can you give us an example showing that RTRIM() doesn't remove charaters from the right????

Are you sure they are spaces? Even thought they may look like spaces they might be something else.

This is a varchar2(60) field which has extra spaces at the end.

length(fieldname) = length(rtrim(fieldname))

substr(fieldname,1,length(fieldname)) displays spaces (visible on toad and in an unix file)

substr(fieldname,1,30) displays 30 chars only. (field ends at the last char in toad as well as unix)

length(fieldname) = 30

Let's say a field's name is Functionname and it's values are:

OracleProgramming
OracleplsqlProgramming


substr(fieldname,1,length(fieldname)) shows something like this on toad:

OracleProgramming |
OracleplsqlProgramming |

and substr(functionname,1,22) on toad shows:

OracleProgramming |
OracleplsqlProgramming|

Here length(OracleplsqlProgramming) = 22

It's the same on an unix file. I can see the spaces( or nulls ??) In unix file, the line splits ....The unix file is created from a shell script. When I substring the fieldname to its length (Ex: 30) it displays normally on unix file. If I don't substring or substring it to length(fieldname), those blanks/nulls/extra chars appear, therefore splitting the line into two seperate lines on unix file

TRIMSPOOL is already ON

Sorry

substr(fieldname,1,length(fieldname)) shows something like this on toad:

Code:

---


OracleProgramming              | 
OracleplsqlProgramming          |

---

I can see spaces/nulls ..

You should have mentioned originally that this is an issue with spooling SQL*Plus data to a file.

If length(fieldname) = length(rtrim(fieldname)) then you have no spaces at the end ... however they are getting into your output file, they are not contained in the actual data, as stored in the database.

How are you generating this file? Give us the exact script and SQL*Plus options.

sql script:


sqlplus -s /nolog << ENDSQL
connect user/password@inst;
set pagesize 0;
set feedback off;
set echo off;
set verify off;
set linesize 100;
spool txtfile;
set trimspool on;
select statement;
spool off;
ENDSQL

Infact it is:


sqlplus -s /nolog << ENDSQL
connect user/password@inst;
set pagesize 0;
set feedback off;
set echo off;
set verify off;
set linesize 100;
set trimspool on; # before spooling
spool txtfile;
select statement;
spool off;
ENDSQL