Thursday, February 24, 2011

Mysql function to remove non-alphanumeric character from a string

0 comments


"remove_non_alphanum_char_f" - this function is used remove non-alphanumeric character from a string
Input: varchar(255)
Output: varchar(255)

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

delimiter //
DROP FUNCTION IF EXISTS remove_non_alphanum_char_f //
CREATE FUNCTION remove_non_alphanum_char_f (prm_strInput varchar(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE v_char VARCHAR(1);
  DECLARE v_parseStr VARCHAR(255) DEFAULT ' ';
 
WHILE (i <= LENGTH(prm_strInput) )  DO
 
  SET v_char = SUBSTR(prm_strInput,i,1);
  IF v_char REGEXP  '^[A-Za-z0-9 ]+$' THEN  #alphanumeric
    
        SET v_parseStr = CONCAT(v_parseStr,v_char);  

  END IF;
  SET i = i + 1;
END WHILE;
RETURN trim(v_parseStr);
END
//

------------------
Usage Example:
------------------

select remove_non_alphanum_char_f('Sakib &^*))( scored a brilliant ---=== %% century') ;
Sakib  scored a brilliant   century
1 row in set (0.00 sec)

Leave a Reply

Labels