Home » SQL & PL/SQL » SQL & PL/SQL » extract string and compare
extract string and compare [message #682106] Mon, 05 October 2020 10:48 Go to next message
desmond30
Messages: 35
Registered: November 2009
Member
create table mtest (x number,y varchar2(1000), z varchar2(1000));

insert into mtest values(1, '[AB|UUU/WWW|DATA]','[XY|UUU/WWW|DATA1]');

insert into mtest values(2, '[AB|AAA/BBB|DATA]','[TR|UUU/WWW|DATA1]');

insert into mtest values(3, '[AB|CCC/XXX|DATA]','[XY|CCC/XXX|TA1]');

commit;
I am trying to write a case statement that returns Y or N

Y -- compare y and z columns from above, compare the text between first '|' and second '|'

(example, for row 1, UUU/WWW from column y matches with UUU/WWW of column z, so case gives Y, else give N

so need to find out the text between '|' in both the columns y and z for comparison

I tried this so far using substr and instr, but it does not give me the 'UUU/WWW' I am looking for

select substr('[AB|UUU/WWW|DATA]', INSTR ('[AB|UUU/WWW|DATA]', '|',1,1)+1, INSTR ('[AB|UUU/WWW|DATA]', '|',1,2))
from dual

My idea was, use substr and say, ok, start searching the string with 1st occurance of '|'...and continue until the 2nd occurance of '|'....
Re: extract string and compare [message #682107 is a reply to message #682106] Mon, 05 October 2020 11:29 Go to previous messageGo to next message
John Watson
Messages: 8424
Registered: January 2010
Location: Global Village
Senior Member
orclz> select substr('[AB|UUU/WWW|DATA]', INSTR ('[AB|UUU/WWW|DATA]', '|',1,1)+1, INSTR ('[AB|UUU/WWW|DATA]', '|',1,2) -  INSTR ('[AB|UUU/WWW|DATA]', '|',1,1)-1) from dual;

SUBSTR(
-------
UUU/WWW

orclz>
Re: extract string and compare [message #682108 is a reply to message #682106] Mon, 05 October 2020 11:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67538
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Hoping that, in the opposite of your previous topics, you will give a feedback.

Re: extract string and compare [message #682112 is a reply to message #682107] Mon, 05 October 2020 15:24 Go to previous message
desmond30
Messages: 35
Registered: November 2009
Member
This works, Thank you !
Previous Topic: Pass cursor to Procedure
Next Topic: Test Oracle SQL
Goto Forum:
  


Current Time: Tue Nov 24 04:20:57 CST 2020