Hi Tom,
I have a question?could you assist regarding this issue?

1. I have two tables are order table and product table.
2. order table containing two columns are order test and order app.the column data format is
order test order app
---------- ---------
1.[input] ----> xyz
2.[abc] ---- > 123
3.[jkl] ----> asd

3.product table containing single column product test.the column data format is below,

product test
------------
1.john[input]
2.Michel[abc]
3.Ronald[jkl]
4.my question is I need to mapping the order test and product text columns from both tables .If square brackets text is matching

example:
order test product test
---------- -----------
1.[input] john[input]
2.[abc] Michel[abc]
3.[jkl] Ronald[jkl]
If mapping square brackets text including square brackets from both columns(order test,product test) ,If it is matches I need to replace product test column data with order app data.
similar like this.

order test product test output(product test)
---------- ----------- ---------------------
1.[input] john[input] johnxyx
2.[abc] Michel[abc] michel123
3.[jkl] Ronald[jkl] Ronaldasd


note:
1.the square brackets data will change dynamically.


I need pl-sql script for this.could you help me.

I will give one example:

I have two tables

table 1 :

create table order( orderid number ,ordertest varchar2 , orderapp varchar2);

I am giving test table data of the above table is like below

orderid ordertest orderapp
-------- --------- ---------
1 [input] xyz
2 [abc] jfg
3 [jkl] asdf

table 2:

create table product(proid number, prodtest varchar2);

the sample test data of the above table is

prodid prodtest
-------- ----------
1 john[input]
2 michel[abc]
3 Ronald[jkl]


My scenario is i need to map the order test and prod test columns. if the portion of the strings is exact match including square brackets from both columns .
I need to repalce prodtest data which having square brackets data with orderapp data.
it is similar like this

prodtest ordertest updated prodtest ------->my result like this i need.
-------- map --------- -----------------------
john[input] ---> [input] jhonxyz
michel[abc] ---> [abc] micheljfg
Ronald[jkl] ---> [jkl] Ronaldasdf





For this i Need pl_sql scripts..

I hope it make sense..

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