====== 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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."ReplaceStr" IS 
{create function 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>