Hi,
I am trying to match and replace exact word using regexp_replace but it is not working the way I expected. I am on oracle database 12c.
Here is the query.
SELECT REGEXP_REPLACE('begin
for x in (select emp_id, sal
from emp)
loop
update emp
set sal=sal+100
where emp_id = x.emp_id;
end loop;
end;','EMP\W',' DG_20160210010850067383 ',1,0,'i')
from dual;
Output:
begin
for x in (select emp_id, sal
from DG_20160210010850067383
loop
update DG_20160210010850067383 set sal=sal+100
where emp_id = x.emp_id;
end loop;
end;
Expected output: on 3rd line need closing bracket after the table name. But regexp_replace is replacing the bracket also. I tried different approaches but not able to find the solution.
1.begin
2.for x in (select emp_id, sal
3.from DG_20160210010850067383)
4.loop
5.update DG_20160210010850067383 set sal=sal+100
6.where emp_id = x.emp_id;
7.end loop;
8.end;
I appreciate any help to resolve this issue.
Thanks
Arya