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_at_ucdavis.edu
> <mailto:wjwagman_at_ucdavis.edu>> 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 <http://www.pythian.com/blogs>
>

--
http://www.freelists.org/webpage/oracle-l