So you want to:
- join the two tables where the ordertest string appears in the prodtest description
- For the rows returned, replace the ordertest string with orderapp in the output
?
If so, you can do this by:
- Join on instr(prodtest, ordertest)
- Using replace to switch the strings over in your result:
create table orders( orderid number ,ordertest varchar2(20) , orderapp varchar2(20));
create table product(proid number, prodtest varchar2(20));
INSERT INTO orders values (1, '[input]', 'xyz');
INSERT INTO orders values (2, '[abc]', 'jfg');
INSERT INTO orders values (3, '[jkl]', 'asdf');
INSERT INTO product VALUES (1, 'john[input]');
INSERT INTO product VALUES (2, 'michel[abc]');
INSERT INTO product VALUES (3, 'Ronald[jkl]');
select ordertest || ' ' || replace(prodtest, ordertest, orderapp)
from product p
join orders o
on instr(prodtest, ordertest) > 0;
ORDERTEST||''||REPLACE(PRODTEST,ORDERTEST,ORDERAPP)
[input] johnxyz
[abc] micheljfg
[jkl] Ronaldasdf