Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

using regexp_replace to remove html tags and  

Gor_MahiaMar 10 2014 — edited Mar 11 2014

Hi,

I am trying to use regular expression to remove any html tags/  from a string replacing them with nothing as shown below,

sample= if i enter "hello to the world of<u><p><br>  apex whats coming up" i should get this==> "hello to the world of apex whats coming up"

consider query as, select regexp_replace(string, any html tags/ , 'i') from dual,


so how do i write the expression?

oracle 11g rel2,

thank you.
This post has been answered by KarK on Mar 11 2014
Jump to Answer

Comments

Manik

Check if this works out for you..

WITH t


     AS (SELECT '"hello to the world of<u><p><br>  apex whats coming up"' str


           FROM DUAL)


SELECT REGEXP_REPLACE (str, '<[^>]*>')


  FROM t;

Output:

--------

"hello to the world of  apex whats coming up"

Cheers,

Manik.

2 people found this helpful
Gor_Mahia

Hi Manik,

what about & nbsp ; ie notation for spaces also to be removed from string? I entered it before but somehow cant display. ..regards

Manik

Ok, may be this:

set define off


WITH t
     AS (SELECT '"hello to the world of<u><p><br>  apex whats coming &npsb; up"' str
           FROM DUAL)
SELECT REPLACE(REGEXP_REPLACE (str, '<[^>]*>'),'&npsb;')
  FROM t;

  or may be this :

set define off


WITH t
     AS (SELECT '"hello to the world of<u><p><br>  apex whats&npsb; coming &npsb; up"' str
           FROM DUAL)
SELECT REGEXP_REPLACE (str, '<[^>]*>|&npsb;')
  FROM t;

Cheers,

Manik.  

odie_63

Use UTL_I18N to unescape back entity references :

SQL> with t as (


  2    select '&lt;hello to the world of<u><p><br> apex &amp; Oracle whats coming up&gt;' str


  3    from dual


  4  )


  5  select utl_i18n.unescape_reference(


  6           regexp_replace (str, '<[^>]+>')


  7         )


  8  from t;




UTL_I18N.UNESCAPE_REFERENCE(RE


--------------------------------------------------------------------------------


<hello to the world of apex & Oracle whats coming up>
1 person found this helpful
KarK
Answer

Hi,

Can you post the exact string you want to replace.

If you have a string with &npsb, then you need to escape the '&' symbol (set escape '\' and add '\' infront of & in your string) or need to undefine it(as mentioned by MANIK)else it will prompt value for it.

Assuming your string has both the string '&npsb;' and '& npsb;'



set escape '\'


WITH t(STR)


     AS (SELECT 'hello to the world of<u><p><br>& npsb;  apex whats coming \&npsb; up' 


           FROM DUAL)


SELECT REGEXP_REPLACE(STR, '<[^>]*>|&npsb;|& npsb;')


  FROM t;




OUTPUT:




hello to the world of  apex whats coming  up








set define off


WITH t(STR)


     AS (SELECT 'hello to the world of<u><p><br>& npsb;  apex whats coming &npsb; up' 


           FROM DUAL)


SELECT REGEXP_REPLACE(STR, '<[^>]*>|&npsb;|& npsb;')


  FROM t;




OUTPUT:




hello to the world of  apex whats coming  up
Marked as Answer by Gor_Mahia · Sep 27 2020
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 8 2014
Added on Mar 10 2014
5 comments
35,872 views