Re: sql*plus column format remove whitespace

[Quoted] 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 Received on Mon Nov 01 2004 - 15:39:31 CET