Re: Spool file - Triming columns

"Frank van Bortel" <fvanbortel_at_netscape.net> wrote in message news:cofjdv$n0s$1_at_news3.zwoll1.ov.home.nl...
| 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.

|

I would say you were probably just having a starbucks-deprivation moment ;-)

++ mcs Received on Mon Nov 29 2004 - 14:48:16 CST