====== pears.GetTextFromHTML ====== Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace. ===== Original 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",'',1) = 0 then
    return "plainText"
  end if;
  set "plainText" = "right"("plainText","length"("plainText")-"charindex"('',"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",' ','');
  set "plainText" = "replace"("plainText",'&','&');
  set "plainText" = "replace"("plainText",'>','>');
  set "plainText" = "replace"("plainText",'<','<');
  set "plainText" = "replace"("plainText",'"',"char"(34));
  set "plainText" = "replace"("plainText",''',"char"(39));
  set "plainText" = "replace"("plainText",'¢',"char"(163));
  set "plainText" = "replace"("plainText",'£',"char"(163));
  set "plainText" = "replace"("plainText",'€',"char"(128));
  set "plainText" = "replace"("plainText",'¥',"char"(165));
  set "plainText" = "replace"("plainText",'©',"char"(169));
  set "plainText" = "replace"("plainText",'®',"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