RE: SQL question

Thanks to all those who offered suggestions. It appears that indeed this is the 
nature of SQL*Plus. Jack Van Zanen's suggestion...

select foo||','||bar||,||baz
from mytable
/
is the only way to do what I want but I was hoping I could achieve what I was 
after without this kind of manipulation, clearly it isn't possible. I 
appreciate the replies.

Thanks.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of William Robertson
Sent: Tuesday, October 14, 2008 12:12 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: SQL question

Agreed, this has nothing to do with SQL. SQL*Plus reports results in columns - 
that's the way it's built and you can't turn it off by changing settings. "SET 
COLSEP ," merely changes the character between columns from a single space to a 
single comma, it's not going to collapse each row into a comma-separated list. 
You have to do that manually.

btw those CHAR(1) columns are asking for trouble. I would make them 
VARCHAR2(1). You are not supposed to actually use CHAR, it's there to look good 
on ANSI compatibility checklists.

-----Original message-----
From: Mark Brinsmead
Date: 14/10/08 05:36
Actually, this does not appear to be a "SQL" question, nearly so much as a 
SQL*Plus question.  Unless I am completely mising something, anyway.

SQL*Plus is trying to do you a favour, and format your output "nicely" in human 
readable columns.  There is nothing wrong with your data, nor with your query 
-- attempts to use INSTR and SUBSTR to remove the trailing blanks from your 
data are doomed to failure.  Because there are no blanks.  Remember, SQL*Plus 
is really -- as much as anything -- a report writing tool.

Sadly, I don't have the answer right at my fingertips, but the prior suggestion 
of COLSEP was probably on the right track (assuming that it is not, in fact, 
the correct answer).  A few minutes with the SQL*Plus documentation ought to 
get you what you need.

On Mon, Oct 13, 2008 at 5:34 PM, William Wagman 
<wjwagman@xxxxxxxxxxx<mailto:wjwagman@xxxxxxxxxxx>> wrote:
Greetings,

Running Oracle 10.2.0.3.0 on RHEL4, 64-bit.

Table:

foo VARCHAR2(300)
bar CHAR(1)
baz CHAR(1)

Data:

somelongishstring,A,B
shorterstring,A,C
shorter,A,D

Query:

select foo,bar,baz
from mytable
/

Output:

somelongishstring                                                               
   ,A,B
shorterstring                                                                   
   ,A,C
shorter                                                                         
   ,A,D

Desired:

somelongishstring,A,B
shorterstring,A,C
shorter,A,D

SQL> select substr(foo,0,instr(foo,' ',0,1)),bar,baz from mytable;

Does not work, because there's no space in the data, INSTR(FOO,' ') returns 
zero and so SUBSTR(foo,0,0) returns an empty string. > Is there any way to 
achieve the desired result in vanilla SQL*Plus short of the following (which 
looks like line noise), or resorting to PL/SQL or some other language (Perl, 
Java, ...)?

select foo||','||bar||','||baz
from mytable

Other related posts: