SQL question

  • From: William Wagman <wjwagman@xxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 13 Oct 2008 16:34:16 -0700

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


Other related posts: