Re: sql*plus column format remove whitespace

ed.prochak_at_magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.0411021016.562edc9b_at_posting.google.com>...
> Please bottom post.
[Quoted] Sorry, I am new to usenet, I thought the etiquette was to top post. Atleast thats what I do in email.

>
> 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.
Not if you are going to send the output in a CSV file email attachment for a viewing and using in Excel.

>
> 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.

TRUNCATE option would not remove the trailing space after all columns. I need a specific column format to not pad the column data with trailing whitespace. As far as I have searched there is nothing available which I could find.

>
> Or use the combination of COLSEP and TRIM()
>
> SET COLSET ','
> SELECT RTRIM(cola) columnone, RTRIM(colb) columntwo
> FROM yourtable ;
>
> should do as you need.
Again these work on the actual data, but do not control the SQL*PLUS output display

>
> Ed

cheers,
Alok. Received on Thu Nov 04 2004 - 17:50:23 CET