Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2017
    Posts
    3

    Answered: Remove html tags from database

    Hi,

    I have a requirement where I need to remove html tags from rows in a table.

    There are more than one instance of html tags (of varying length) in each row.
    Here is an example of a row -
    TEXT1 <span style="font-family:calibri,sans-serif; font-size:11.0pt">TEXT2 </span>
    <span style="font-family:calibri,sans-serif; font-size:11.0pt">TEXT3 </span>
    <span style="font-family:calibri,sans-serif; font-size:11.0pt">TEXT4</span>.

    output needed - TEXT1 TEXT2 TEXT3 TEXT4

    I have written a UDF in DB2 while goes like this -
    --<ScriptOptions statementTerminator="!"/>

    CREATE or replace FUNCTION test12 (htmltext varchar(8000))
    RETURNS varchar(8000)
    F1: BEGIN ATOMIC
    DECLARE start_1,end_1, length_1 Bigint;
    DECLARE html_1 varchar(8000);

    SET start_1 = locate('<',htmltext);
    SET end_1 = locate('>',htmltext,locate('<',htmltext));
    SET length_1 = (end_1-start_1)+1;
    SET html_1 = insert(htmltext,start_1,length_1,'');


    while (start_1 > 0 and end_1 >0 and length_1 >0) do


    set start_1 = locate('<',html_1);
    set end_1 = locate('>',html_1,locate('<',html_1));
    set length_1 = (end_1-start_1)+1;
    set html_1 = insert(html_1,start_1,length_1,'');
    END while;

    RETURN LTRIM(RTRIM(html_1));

    END!

    This is replacing only first instance of tags. can someone please suggest how to deal with this? Thanks!

  2. Best Answer
    Posted by mark.bb

    "Hi,

    Try this:
    Code:
    select xmlserialize(xmlquery('$D/d//text()' passing xmlparse(document '<d>'||str||'</d>') as "D") as varchar(4000))
    from table(values 
    'TEXT1 <span style="font-family:calibri,sans-serif; font-size:11.0pt">TEXT2 </span>
    <span style="font-family:calibri,sans-serif; font-size:11.0pt">TEXT3 </span>
    <span style="font-family:calibri,sans-serif; font-size:11.0pt">TEXT4</span>'
    ) t (str)
    "


  3. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    Try this:
    Code:
    select xmlserialize(xmlquery('$D/d//text()' passing xmlparse(document '<d>'||str||'</d>') as "D") as varchar(4000))
    from table(values 
    'TEXT1 <span style="font-family:calibri,sans-serif; font-size:11.0pt">TEXT2 </span>
    <span style="font-family:calibri,sans-serif; font-size:11.0pt">TEXT3 </span>
    <span style="font-family:calibri,sans-serif; font-size:11.0pt">TEXT4</span>'
    ) t (str)
    Regards,
    Mark.

  4. #3
    Join Date
    Feb 2017
    Posts
    3
    Thank you Mark.

    This solution works perfectly fine. Is there a way to pass a column value instead of literal string? I tried a few things but ended up with errors.

    Thank you.

  5. #4
    Join Date
    Feb 2017
    Posts
    3
    Please ignore my previous message, I was able to pass the value from columns. Errors were due to some other characters, Once i removed those it worked perfectly fine.

    Thank you very much, appreciate your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •