Home » SQL & PL/SQL » SQL & PL/SQL » extract string and compare
extract string and compare [message #682106] |
Mon, 05 October 2020 10:48 |
desmond30
Messages: 41 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 |
John Watson
Messages: 8938 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>
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 01:23:27 CDT 2024
|