Home » SQL & PL/SQL » SQL & PL/SQL » substring help
substring help [message #738] |
Thu, 28 February 2002 15:21 |
Ravi
Messages: 251 Registered: June 1998
|
Senior Member |
|
|
Hi,
How do I eleminated to not to selected when i do following query. I am getting 8 records, but technically I have to get 6 only with the values. Can any body help me ?
create table ravi( a varchar2(10),b varchar2(10),c varchar2(10));
insert into ravi values('100,200','xx,yy','test');
insert into ravi values(null,'aa,bb','test');
insert into ravi values('555,666',null,'test');
insert into ravi values(null,null,null);
insert into ravi values(null,null,'test');
select c,substr(a,1,instr(a,',')-1),substr(b,1,instr(b,',')-1) from ravi
union
select c,substr(a,instr(a,',')+1),substr(b,instr(b,',')+1) from ravi
/
|
|
|
|
Re: substring help [message #749 is a reply to message #738] |
Fri, 01 March 2002 09:30 |
monir
Messages: 22 Registered: February 2002
|
Junior Member |
|
|
try this
select c,substr(a,1,instr(a,',')-1),substr(b,1,instr(b,',')-1) from ravi
where substr(a,1,instr(a,',')-1) is not null
or substr(b,1,instr(b,',')-1) is not null
union
select c,substr(a,instr(a,',')+1),substr(b,instr(b,',')+1) from ravi
where substr(a,1,instr(a,',')+1) is not null
or substr(b,1,instr(b,',')+1) is not null
/
|
|
|
Goto Forum:
Current Time: Fri Apr 26 09:13:34 CDT 2024
|