Re: Spool file - Triming columns

Mark C. Stock wrote:
> "Frank van Bortel" <fvanbortel_at_netscape.net> wrote in message
> news:cof01r$5d4$3_at_news6.zwoll1.ov.home.nl...
> | Matthieu wrote:
> | > Hello,
> | >
> | > I want to make an extraction into a flat file of the folowing table:
> | >
> | > ART
> | > ====
> | > CO_ART VARCHAR(12)
> | > LI_ART VARCHAR(50)
> | > CO_MOD NUMBER(1)
> | >
> | > I want the flat file looks like:
> | >
> | > 1529#PRODUCT 1#1
> | > 1254899#PRODUCT 2222#1
> | > 12#PROD 1235#2
> | >
> | > I use set COLSEP '#' for the separator but how can I make my columns
> trimed.
> | > By default it looks like:
> | >
> | > 1529 #PRODUCT 1 #1
> | > 1254899 #PRODUCT 2222 #1
> | > 12 #PROD 1235 #2
> | >
> | > Thanks for help.
> | >
> | > Matthieu
> | >
> | >
> | select trim(col1)...
> |
> | --
> |
> | Regards,
> | Frank van Bortel
>
> actually, you'll need to concatenate the columns:
>
> SQL> select *
> 2 from dept;
> ...
>
> DEPTNO DNAME LOC
> ---------- -------------- -------------
> 10 ACCOUNTING New York, NY
> 20 RESEARCH DALLAS
> 30 SALES CHICAGO
>
> SQL> select trim(deptno), trim(dname), trim(loc)
> 2 from dept;
> ...
>
> TRIM(DEPTNO) TRIM(DNAME) TRIM(LOC)
> ---------------------------------------- -------------- -------------
> 10 ACCOUNTING New York, NY
> 20 RESEARCH DALLAS
> 30 SALES CHICAGO
>
> SQL> select deptno||'#'||dname||'#'||loc
> 2 from dept;
> ...
>
> DEPTNO||'#'||DNAME||'#'||LOC
> ---------------------------------------------------------------------
> 10#ACCOUNTING#New York, NY
> 20#RESEARCH#DALLAS
> 30#SALES#CHICAGO
>
> SQL*Plus always outputs one fixed-width column per expression, based on the
> maximum length it determines from the database when it processes the SQL, or
> based on COLUMN format settings -- to override this, your SELECT statement
> must submit a single expression
>
> ++ mcs
>
>

You are correct; I assumed from the output given by the OP, that he already had that part. Shouldn't have.

But, for a lame excuse, I did not say he could just select columns as select trim(col1), trim(col2), either...

-- 
Regards,
Frank van Bortel