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 'SQL PL/SQL' started by sania, Oct 10, 2018 at 2:28 PM.

  1. sania

    sania Active Member

    Messages:
    10
    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.

    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.


    while running query it is asking for values of &amp, &lt, &gt ,&quot and after I input it all the times with respective values like -&,<,>,"

    this code returned below output..

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

    I want to remove that extra semicolon.

    is there any alternative for this code, becoz I am using it(oracle DB as backend) in sample cube , and it is taking very long time to process.


    please help.
     
    Last edited: Oct 11, 2018 at 7:53 AM
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,635
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post what it iS doing. Then someone may be able to assist you.