pears.ReplaceStr

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
}