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!

Alternative code to eliminate data between<> and replace few things

Discussion in 'SQL PL/SQL' started by sania, Oct 11, 2018 at 12:31 PM.

  1. sania

    sania Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    india
    Hi friends,

    The below code eliminates text between <> angular brackets,
    and replces &amp; with &
    &quot; with "
    &lt; with <
    &gt; with >
    and removes text between < >

    But this taking very long time to run, I am looking for any alternative for this.
    please suggest

    create or replace FUNCTION format_data
    (pstr IN CLOB)

    RETURN CLOB
    AS

    vstr_in CLOB := p_str;
    vstr_out CLOB;

    BEGIN
    WHILE INSTR (vstr_in, '>') > 0 LOOP
    v_string_out := vstr_out
    || SUBSTR (vstr_in, 1, INSTR (vstr_in, '<') - 1);
    v_string_in := SUBSTR (vstr_in, INSTR (vstr_in, '>') + 1);
    END LOOP;
    vstr_out := v_string_out || v_string_in;
    vstr_out := REPLACE (vstr_out,'&amp;','&');
    vstr_out := REPLACE (vstr_out,'&quot;','"');
    vstr_out := REPLACE (vstr_out,'&lt;','<');
    vstr_out := REPLACE (vstr_out,'&gt;','>');

    RETURN vstr_out;
    END format_data;

    eg-
    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> &amp; broke his leg &amp; jill came tumbling &lt; no &gt; <comments> after &quot; haha &quot;'


    should be formatted as -

    jack & jill , went up the " hill " to fetch a pail of water , jack fell down & broke his leg & jill came tumbling after " haha "
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,635
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're processing a CLOB, which can be very large in size. There is not much you can do other than possibly changing your storage to SSD.