SQL question
Date: Mon, 13 Oct 2008 16:34:16 -0700
Message-ID: <2A8185DC02A8CE4C8413E0A26A8A831A01D718B647@XEDAMAIL2.ex.ad3.ucdavis.edu>
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_at_ucdavis.edu
(530) 754-6208
†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Mon Oct 13 2008 - 18:34:16 CDT