Thursday, February 24, 2011

Mysql function to replace multiple occurrences of a character from a string

0 comments

"replace_multiple_occurrences_of_char_f"- this function is used to replace one or more occurances of a character in a string with a given character.
For example, in the string "ahmad------------iftekhar-rumman" , we can replace all the occurances of '-'(hyphen) with ' '(space) and take the output as "ahmad iftekhar rumman"

-------------------------
Input Parameters:
-------------------------
prm_strInput:  varchar(255) - the string
prm_from_char: varchar(1) - character to replace in the given string
prm_to_char: varchar(1) - replace with character

---------
Return:
-----------
varchar(255)

---------------
Source Code:
------------------

delimiter //
DROP FUNCTION IF EXISTS replace_multiple_occurrences_of_char_f //
CREATE FUNCTION replace_multiple_occurences_of_char_f (prm_strInput varchar(255), prm_from_char VARCHAR(1),prm_to_char VARCHAR(1))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE nNumSpaces,spacePos1,spacePos2,v_strLen SMALLINT;
DECLARE i INT DEFAULT 0;
DECLARE v_parseStr varchar(255) DEFAULT prm_to_char;
SET spacePos1 = 0;
SET spacePos2 = 1;
SET prm_strInput = TRIM(prm_strInput);
SET nNumSpaces = LENGTH(prm_strInput) -  LENGTH(REPLACE(prm_strInput,prm_from_char,''));
SET prm_strInput = CONCAT(prm_from_char,prm_strInput,prm_from_char);
WHILE (i <= nNumSpaces)  DO
  
   SET spacePos1 = LOCATE(prm_from_char,prm_strInput,spacePos1 + 1);
  
   SET spacePos2 = LOCATE(prm_from_char,prm_strInput,spacePos1 + 1);
   SET v_strLen = (spacePos2-spacePos1)-1;
   IF v_strLen > 0 THEN
     SET v_parseStr = CONCAT(v_parseStr, SUBSTRING(prm_strInput,spacePos1 + 1, v_strLen ),prm_to_char);
   END IF;
   SET i = i + 1;
END WHILE;
  
   SET v_parseStr = SUBSTRING(v_parseStr, 2, length(v_parseStr)-2);
   return v_parseStr;
END
//
------------------
Usage Example:
------------------
select replace_multiple_occurences_of_char_f('ahmad------------iftekhar-rumman','-',' ');
ahmad iftekhar rumman

Leave a Reply

Labels