Re: sql*plus column format remove whitespace

Turkbear <john.g_at_dot.spamfree.com> wrote in message news:<1099319880.ySo6Ip/vl6AQltETDbAORw_at_teranews>...
> alok_bisani_at_yahoo.com (Alok Bisani) wrote:
>
> >Yes I know. But what is the format parameter to this command which
> >will give a trimmed output in SQL*PLUS query result? As far as I know
> >the only way to get a trimmed CSV output in SQL*PLUS is to query a
> >single string concatenating with ',' all the actual columns required.
> >But then that may have limitations to size of the string (4000 for
> >varchar2)??
> >
> >ed.prochak_at_magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.0410281119.191766ff_at_posting.google.com>...
> >> alok_bisani_at_yahoo.com (Alok Bisani) wrote in message news:<f28905db.0410280225.23ca2f21_at_posting.google.com>...
> >> > Hi,
> >> > Is there a column format in SQL*PLUS to trim whitespace? For eg.
> >> > the following query output
> >> > SQL> select owner, table_name, tablespace_name, cluster_name from
> >> > all_tables where rownum < 2;
> >> >
> >> > OWNER TABLE_NAME
> >> > ------------------------------ ------------------------------
> >> > TABLESPACE_NAME CLUSTER_NAME
> >> > ------------------------------ ------------------------------
> >> > SYS UNDO$
> >> > SYSTEM
> >> >
> >> >
> >> > SQL>
> >> >
> >> > should be something like this.
> >> > SQL> select owner, table_name, tablespace_name, cluster_name from
> >> > all_tables where rownum < 2;
> >> >
> >> > OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
> >> > ----- ---------- --------------- ------------
> >> > SYS UNDO$ SYSTEM
> >> >
> >> >
> >> > SQL>
> >> >
> >> > This is required to output a CSV file from a query. I can do a string
> >> > concatenation of all the columns with a comma in between, but sure
> >> > there is a 4000 or someother limit to that?
> >>
> >> There is a command for formatting, called, strangely enough, COLUMN.
> >>
> >> HTH,
> >> ed
> Set pagesize 0
> set linesize 1000
> set heading off
> set feedback off
> spool 'c:\myfiles\outpiut.csv'
> then
> Use the TRIM() function for each fields and spool out ot the text file..
>
> select trim(f1),trim(f2), etc..
>
> spool off

No, "trim" function on query data column does not change the SQL*PLUS output whitespace after the column. You have to use the COLUMN FORMAT SQL*PLUS command, but I am afraid there is no option in it to not display trailing whitespace. Received on Thu Nov 04 2004 - 17:45:04 CET