TechTip - Oracle SQL query to replace strings in clob column

If you are looking for oracle query to find and replace a string in clob column in bulk. You can find same tip below:

Create table having clob column.

create table MyClobTable ( column1 int, clob_column clob );

Create procedure to perform insertion in table

create or replace procedure MyProc( proc_column1 in int, proc_text in varchar2 ) as begin insert into MyClobTable values ( proc_column1, proc_text ); end;

Insert below two records in table.

exec MyProc(1, 'I am Narendra Verma and currently living in Atlanta. I visited a lot of places in Atlanta. Atlanta is a nice city in US.' ); exec MyProc(2, It is a great time to be in the City of Atlanta' ); commit;

Check if rows are inserted.

select * from MyClobTable;

Output:

1	I am Narendra Verma and currently living in Atlanta. I visited a lot of places in Atlanta. Atlanta is a nice city in US.
2	It is a great time to be in the City of Atlanta

Execute below to replace ‘Atlanta’ with 'Alpharetta' in all rows.

MERGE INTO MyClobTable A      USING (SELECT column1,                    TO_CLOB (REPLACE (clob_column, 'Atlanta', 'Alpharetta'))                       AS updated_string               FROM MyClobTable              WHERE clob_column LIKE '%Atlanta%'             )  B         ON (A.column1 = B.column1) WHEN MATCHED THEN    UPDATE SET A.clob_column = B.updated_string;

Check if 'Atlanta' word is replaced with 'Alpharetta' in all rows.

select * from MyClobTable;

Output:

1	I am Narendra Verma and currently living in Alpharetta. I visited a lot of places in Alpharetta. Alpharetta is a nice city in US.
2	It is a great time to be in the City of Alpharetta

Hope this tech tip helped you.