====== pears.ReplaceStr ======
<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"."ReplaceStr"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "mainstring" char(255),in "replacestring" char(255),in "withwhat" char(255),in "times" tinyint ) 
returns varchar(255)
begin
  //
  // Variable declarations
  //
  declare "newstring" varchar(255);
  declare "togo" tinyint;
  declare "beforestring" varchar(255);
  declare "afterstring" varchar(255);
  declare "pos" tinyint;
  declare "lenreplace" tinyint;
  declare "lennewstring" tinyint;
  declare "lenwithwhat" tinyint;
  declare "startpos" tinyint;
  //
  // Set initial values
  //
  set "lenreplace" = "length"("replacestring");
  set "newstring" = "mainstring";
  set "lenwithwhat" = "length"("withwhat");
  set "pos" = 0;
  set "startpos" = 1;
  // Check for null or empty string
  if "mainstring" is null or "mainstring" = '' then return("mainstring")
  end if;
  // Set counter, if not replacing all
  if "times" <> 0 then
    set "togo" = "times"
  end if;
  //
  // Loop for replace_text
  //
  "replace_text": loop
    // Find position of (next) occurance
    set "pos" = "locate"("newstring","replacestring","startpos");
    // Check for no (more) occurances
    if "pos" = 0 then leave "replace_text"
    end if;
    // Check for done required number of replacements, if not replacing all
    if "times" <> 0 then
      if "togo" = 0 then leave "replace_text"
      end if end if;
    set "lennewstring" = "length"("newstring");
    // Find text before and after text to be replaced
    set "beforestring" = "left"("newstring","pos"-1);
    set "afterstring" = "right"("newstring","lennewstring"-("pos"+"lenreplace"-1));
    // Create new string with replaced text
    set "newstring" = "beforestring" || "withwhat" || "afterstring";
    // Set new search starting position
    set "startpos" = "pos"+"lenwithwhat";
    // Decrement number of replacements counter, if not replacing all
    if "times" <> 0 then
      set "togo" = "togo"-1
    end if
  end loop "replace_text";
  return("newstring")
end
</code>