I have a clob with HTML document. How to replace all scripts in it with text?

Question

for example I have the following HTML document:

<b>Hello world</b>
<script>window.alert('Hello world')</script>

And I need to to get:

<b>Hello world</b>
&lt;script&gt;window.alert('Hello world')&lt;/script&gt;

I want to write an Oracle PL/SQL function that will make such conversion.

I use Oracle 11. I tried using regexp and Oracle XML functions but unsuccessfully.

Answer 1

Maybe you can use the (built-in) REPLACE function. Example (tested w/ Oracle 11g, see dbfiddle):

Table for testing

create table myclobs ( col clob ) ;

insert into myclobs ( col )
values ( '<b>Hello world</b><script>window.alert(''Hello world'')</script>');

select * from myclobs ;

-- result
COL
<b>Hello world</b><script>window.alert('Hello world')</script>

Query

select
  replace( 
    replace( col, '</script>','&lt;/script&gt;' )
  , '<script>','&lt;script&gt;' 
  ) as scripttagsreplaced
from myclobs ;

-- result
SCRIPTTAGSREPLACED
<b>Hello world</b>&lt;script&gt;window.alert('Hello world')&lt;/script&gt;

( The "inner" REPLACE converts the end tags, and the "outer" REPLACE converts the start tags. ) If this works for you, and you still want to write your own function, just compile the following:

-- rst: Replace the Script Tags
create or replace function rst( theclob clob )
return clob
is
begin  
  return  replace( 
    replace( theclob, '</script>','&lt;/script&gt;' ) 
  , '<script>','&lt;script&gt;' 
  ) ;
end ;
/

With this in place, you can SELECT ... as follows:

select rst( col ) from myclobs ;