select foo||','||bar||,||baz from mytable / 2008/10/14 William Wagman <wjwagman@xxxxxxxxxxx> > 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 > > > -- J.A. van Zanen