Re: SQL question
- From: "Mark Brinsmead" <pythianbrinsmead@xxxxxxxxx>
- To: wjwagman@xxxxxxxxxxx
- Date: Mon, 13 Oct 2008 22:36:51 -0600
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>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
> /
>
> Thanks.
> Bill Wagman
> Univ. of California at Davis
> IET Campus Data Center
> wjwagman@xxxxxxxxxxx
> (530) 754-6208
>
>
>
--
Cheers,
-- Mark Brinsmead
Senior DBA,
The Pythian Group
http://www.pythian.com/blogs
Other related posts:
