sql*plus column format remove whitespace
Alok Bisani
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?
Michel Cadot
"Alok Bisani" <alok_...@yahoo.com> a écrit dans le message de
news:f28905db.04102...@posting.google.com...
To get a CSV output format use:
set underline off
set colsep ';'
This does not trim whitespaces but you'll get fields separated with ;
--
Regards
Michel Cadot
Ed prochak
There is a command for formatting, called, strangely enough, COLUMN.
HTH,
ed
Alok Bisani
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.pr...@magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.04102...@posting.google.com>...
Turkbear
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
Turkbear
OOPs - forgot the set colsep part that other posters mentioned..
Also apparently forgot to spell check..
Ed prochak
alok_...@yahoo.com (Alok Bisani) wrote in message news:<f28905db.04110...@posting.google.com>...
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
Alok Bisani
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.
Alok Bisani
> Please bottom post.
Sorry, I am new to usenet, I thought the etiquette was to top post.
Atleast thats what I do in email.
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.
Ed prochak
> ed.pr...@magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.04110...@posting.google.com>...
> > Please bottom post.
> Sorry, I am new to usenet, I thought the etiquette was to top post.
> Atleast thats what I do in email.
>
>
joining this newsgroup might not have the history of the thread
available. Bottom posting allows reviewing the context in the order it
was written.
[bunch of old stuff deleted]
> > 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.
I had not realized that. Whenever I loaded such output into Xcel, I
used fixed sized columns, which trim the trailing spaces. I just
tested and you are right, give Xcel a csv file and it loads it WITH
the trailing spaces.
>
>
> >
> > 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.
Yes, I think you are stuck, as far as using SQL*Plus. You'll have to
do a little post processing to trim things up. A sed or PERL srip
should do it without much trouble.
>
> >
> > 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.
Sorry I didn't solve your problem. I've just used fixed field size
loads so much I forgot how stubborn SQLPLUS could be.
Have a good day.
ed