pears.GetTextFromHTML [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.GetTextFromHTML ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create function "pears"."GetTextFromHTML"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "htmlText" long varchar ) 
returns long varchar
begin
  declare "plainText" long varchar;
  declare "tagStart" integer;
  declare "tagEnd" integer;
  declare "tagLength" integer;
  declare "tagType" char(20);
  declare "endOfTagName" integer;
  set "plainText" = "htmlText";
  if "locate"("plainText",'<HEAD>',1) = 0 then
    return "plainText"
  end if;
  set "plainText" = "right"("plainText","length"("plainText")-"charindex"('</HEAD>',"upper"("plainText"))-6);
  set "plainText" = "replace"("plainText","char"(13),'');
  set "plainText" = "replace"("plainText","char"(10),'');
  set "plainText" = "replace"("plainText",'',"char"(96));
  set "plainText" = "replace"("plainText",'@',"char"(96));
  while "locate"("plainText",'<',1) > 0 loop
    set "tagStart" = "charindex"('<',"plainText");
    set "tagEnd" = "charindex"('>',"plainText");
    // make tag type always be in upper case, and compensate for the < things 
    set "tagType" = "substring"("upper"("plainText"),"tagStart"+1,"tagEnd"-"tagStart"-1);
    set "tagLength" = "tagEnd"-"tagStart";
    // check to see if there are spaces in the name (there are attributes).
    // if there are get rid of them   
    set "endOfTagName" = "charindex"(' ',"tagType");
    if "endOfTagName" > 0 then
      set "tagType" = "substring"("tagType",1,"endOfTagName")
    end if;
    case "tagType"
    when '/DIV' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/P' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/H1' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/H2' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/H3' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/H4' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/H5' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/H6' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/UL' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/OL' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when 'BR' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/TABLE' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when 'HR' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/ARTICLE' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/ASIDE' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/BLOCKQUOTE' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/FIGCAPTION' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/FIGURE' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/FOOTER' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/FORM' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/HEADER' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/MENU' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/NAV' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/PRE' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/SECION' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10))
    when '/SUMMARY' then set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,"char"(13)+"char"(10)) else
      set "plainText" = "stuff"("plainText","tagStart","tagLength"+1,null)
    end case
  end loop;
  set "plainText" = "replace"("plainText",'&nbsp;','');
  set "plainText" = "replace"("plainText",'&amp;','&');
  set "plainText" = "replace"("plainText",'&gt;','>');
  set "plainText" = "replace"("plainText",'&lt;','<');
  set "plainText" = "replace"("plainText",'&quot;',"char"(34));
  set "plainText" = "replace"("plainText",'&apos;',"char"(39));
  set "plainText" = "replace"("plainText",'&cent;',"char"(163));
  set "plainText" = "replace"("plainText",'&pound;',"char"(163));
  set "plainText" = "replace"("plainText",'&euro;',"char"(128));
  set "plainText" = "replace"("plainText",'&yen;',"char"(165));
  set "plainText" = "replace"("plainText",'&copy;',"char"(169));
  set "plainText" = "replace"("plainText",'&reg;',"char"(174));
  if("isnull"("plainText",'') = '') and("isnull"("htmltext",'') <> '') then
    set "plainText" = 'Could not parse following HTML:'+"char"(13)+"char"(10)+"htmlText"
  end if;
  return "plainText"
end
</code>