pears.ReplaceStr

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