Re: Force TO_CHAR to eliminate the leading blank during display

In article <38330_at_arctic.nprdc.navy.mil>, loffredo_at_nprdc.navy.mil (Rick Loffredo) writes:
> I wrote a small SQLPLUS script to convert the Oracle data to an ASCII file.
> As you can see from the script, I had 1 numeric field in the table. In
> addition, I also put out 4 '|'s as field delimiters. QUESTION: How come there
> is alwayw an extra blank preceeding the numeric field (Argument_Number), even
> though I explicitly set "space 0", and provided the TO_CHAR function with a
> format of '09'.

I think it's the space for the sign of the Number. Remove it with LTRIM.

>
> ******************
> * SQLPLUS Script *
> ******************
>
> spool trargs.txt
> TTITLE off
> set heading off
> set linesize 78
> set pagesize 0
> set feedback off
> set space 0
>
> select
> Function, '|',
> TO_CHAR(Argument_Number,'09'), '|',
> Description, '|',
> Data_Type, '|'
> from TRANSFORMATION_ARGUMENTS order by 1 ASC, 3 ASC;
> spool off
> exit

column Argument_Number format a2

 select

     Function, '|',		
     LTRIM(TO_CHAR(Argument_Number)) Argument_Number, '|',	
     Description, '|',		
     Data_Type, '|'		
     from TRANSFORMATION_ARGUMENTS order by 1 ASC, 3 ASC;

>
> *********
> *Schema *
> *********
>
> CREATE TABLE TRANSFORMATION_ARGUMENTS
> (
> Function CHAR(30) NOT NULL,
> Argument_Number NUMBER(2) NOT NULL,
> Description CHAR(40),
> Data_Type CHAR(1)
> );
>
> ****************************
> *Sample Output: TRARGS.TXT *
> ****************************
>
> Are_Lists_The_Same | 01|First generic list |G|
> Are_Lists_The_Same | 02|Second generic list |G|
> Extract_Sub_List | 01|Original generic list |G|
> Extract_Sub_List | 02|Index of comparison column |I|
>

-- 

================================================================================
                                                                          ^
 Federal Ministry of Science and Research                               B | M
<-------------------------------------------------------------------------+---->
 Computer Center                                                        W | F
                                                                          |
 Klaus-Michael Hatzinger            mail: hatzinger_m_at_bmwf1f.bmwf.gv.at   |
 Bankgasse 1/209                   phone: 0043-222-53120/5188             |
 1014 Vienna, Austria                fax: 0043-222-53120/5155             V
================================================================================