Monday, 18 December 2017

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.