OraFAQ Forum: SQL & PL/SQL » substring help

Home » SQL & PL/SQL » SQL & PL/SQL » substring help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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
/

Report message to a moderator

Re: substring help [message #741 is a reply to message #738]

Thu, 28 February 2002 22:58

THA
Messages: 26
Registered: February 2002

Junior Member

use where condition to eliminate the rows for null value

Report message to a moderator

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
/

Report message to a moderator

Previous Topic:	how do i get a list of all triggers,procedures etc associated with a table
Next Topic:	how do i see the trigger description

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 01:38:31 CDT 2024