Re: Spool file - Triming columns

"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 Received on Mon Nov 29 2004 - 06:00:31 CST