RE: SQL question
- From: William Wagman <wjwagman@xxxxxxxxxxx>
- To: "william@xxxxxxxxxxxxxxxxxxxx" <william@xxxxxxxxxxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Oct 2008 08:51:21 -0700
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:
