Re: sql*plus column format remove whitespace

Please bottom post.

alok_bisani_at_yahoo.com (Alok Bisani) wrote in message news:<f28905db.0411010609.1bfd7ef6_at_posting.google.com>...
> 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

If you want comma separated fields, then set the column separator from '|' to \,' like this
SET COLSEP ',' now a select * from tableX; will have commas between each column.

Why do you think you need to TRIM the data? If you are producing a CSV file, who cares about trailing whitespace? That's an issue for the loader to deal with.

If you know the max length of your output DATA , then you can use the TRUNCATE option on the COLUMN command to minimize the trailing spaces.

Or use the combination of COLSEP and TRIM()

SET COLSET ','
SELECT RTRIM(cola) columnone, RTRIM(colb) columntwo FROM yourtable ;

should do as you need.

  Ed Received on Tue Nov 02 2004 - 12:16:31 CST