SQLPLUS SPOOL - TO_CHAR on NUMBER field adding a trailing space

Question

Hi I am using a sql script to extract data in a file using a spool command. I am converting one column that is numeric to to_CHAR and it is adding an extra space at the end. I have used FM0000 as a format but not sure why the trailing space is added when I convert a number to character.

I am using following in the script

SET SPACE 0
SET LINESIZE 1000
SET TRIMSPOOL ON
--SET TRIMOUT ON
SET TERMOUT OFF
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL /u05/oracle/loadarea/Paragon/iib/data/rdrmst.dat

SELECT TO_CHAR(RDRMST_ODOMETER,'FM000000') FROM TABLE

Please let me know

score 0 · Answer 1 · answered Oct 16 '18 at 20:44

0

Try:

SELECT LTRIM(RTRIM(TO_CHAR(RDRMST_ODOMETER,'FM000000'))) FROM TABLE

LTRIM - removes leading spaces

RTRIM - removes trailing spaces

answered Oct 16 '18 at 20:44

Danial Wayne

348
2
15

Using the FM format mask obviates the need for trimming, but you could just use TRIM( ) as it would remove both leading AND trailing blanks. – Gary_W Oct 16 '18 at 21:08

Gary_W · Answer 2 · 2018-10-16T21:30:20.740

The space is for the sign when TO_CHAR() is used to convert a number to a fixed-width character string. The correct way to handle it is to use the Format Mask format like you are doing. Consider this:

SQL> SELECT '['||TO_CHAR(4000,'0009')||']'    test1,
            '['||TO_CHAR(-4000,'0009')||']'   test2,
            '['||TO_CHAR(4000,'FM0009')||']'  test3,
            '['||TO_CHAR(-4000,'FM0009')||']' test4
     FROM dual;

TEST1   TEST2   TEST3   TEST4
------- ------- ------- -------
[ 4000] [-4000] [4000]  [-4000]

SQL>

SQLPLUS SPOOL - TO_CHAR on NUMBER field adding a trailing space

2 Answers2