1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to use regular expressions in one statement on a column

Discussion in 'Documents Section Discussions' started by sania, Oct 10, 2018.

  1. sania

    sania Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    india
    Hi team,

    I have a column, which stores long text description with special characters of html like <> , &amp; ,&lt; , &gt; ,&amp; ,&quot; .

    for example- 'jack &amp; jill &lt; eliminate this txt &gt; , went up the &quot; hill &quot; to fetch a pail of water , jack fell down<oh no --eliminate> and broke his leg.

    so I need output as

    jack & jill , went up the "hill" to fetch a pail of water, jack fell down and broke his leg.

    so

    &lt; should be replaced with <
    &gt;
    should be replaced with >
    and text between this brackets should be erased.

    replace &quot ; with "
    &amp;
    with &

    I tried like this -
    select replace(
    replace(
    replace(
    replace(
    regexp_replace( 'jack &amp; jill &lt; eliminate this txt &gt; , went up the &quot; hill &quot; to fetch a pail of water, jack fell down<oh no --eliminate> and broke his leg.','<.*?>'),
    '&lt;','<'),
    '&gt;','>'),
    '&amp;','&'),
    '&quot;',' " ') replace_tryout from dual;

    but it is not working as I want.

    please help.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,650
    Likes Received:
    374
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is a SQL*PLus question and it needs to be posted in the SQL forum, not the documents section.

    Repost this in the proper forum to receive assistance.
     
  3. sania

    sania Active Member

    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    india
    Can you please send me appropriate link, where to post this question. I am unable to locate exact forum.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,650
    Likes Received:
    374
    Trophy Points:
    1,430
    Location:
    Aurora, CO