CODEWITHSUNDEEP
sqloracle-database
DIVA
DIVA

Reputation: 579

Oracle replace string inside CLOB with sql as value

I have a table with CLOB datatype with SQL script as value. I want to replace the string inside the sql script value. Please see the below scenario that I'm looking to know:

I have a table called TEST with columns NAME, DEPT_SQL with the below value inside the table:

DEPT_SQL

SELECT * FROM DEPT WHERE DEPT_NAME IN('SALES','MARKETING','FINANCE','IT')

Now, I would like to replace the 'SALES' value with many values 'TODAY_SALES','WEEK_SALES','MONTH_SALES' inside the sql script column value.

Could anyone help me to know about the replacement of single string value with multiple string values?

I tried the below approach but it's giving the proper string to replace:

SELECT name, to_clob(replace(dept_sql,'SALES','TODAY_SALES, WEEK_SALES, MONTH_SALES')) as sql
from test
where sql like '%SALES%' and name='CLOSE'

Thanks in advance.

Upvotes: 0

Views: 3423

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31666

Your replace can be simplified with alternate quoting mechanism (using q) for quotes

SELECT replace(dept_sql, q'{'SALES'}', 
       q'{'TODAY_SALES','WEEK_SALES','MONTH_SALES'}') as sql
FROM   test;

Demo

Upvotes: 2

Related Questions