1) Function
DELIMITER //
CREATE DEFINER=`platformuser`@`localhost` FUNCTION `fn_RemoveSpecialChars`(`pString` CHAR(50)) RETURNS char(50) CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE CleanString varchar(1000) DEFAULT '';
DECLARE c CHAR(1);
DECLARE regx INT(1);
IF pString IS NULL
THEN
RETURN "";
END IF;
SET len = LENGTH( pString );
REPEAT
BEGIN
SET c = MID( pString, i, 1 );
SELECT c REGEXP '[^a-zA-Z0-9 /t/-/_]' into regx;
IF c="" THEN
SET CleanString=CONCAT(CleanString,' ');
ELSEIF regx =0 THEN
SET CleanString=CONCAT(CleanString,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN CleanString;
END//
DELIMITER ;
---------------------------------------------------------------------------
output
SELECT fn_RemoveSpecialChars('welcone world !#@#@');
Result: welcome world
DELIMITER //
CREATE DEFINER=`platformuser`@`localhost` FUNCTION `fn_RemoveSpecialChars`(`pString` CHAR(50)) RETURNS char(50) CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE i, len SMALLINT DEFAULT 1;
DECLARE CleanString varchar(1000) DEFAULT '';
DECLARE c CHAR(1);
DECLARE regx INT(1);
IF pString IS NULL
THEN
RETURN "";
END IF;
SET len = LENGTH( pString );
REPEAT
BEGIN
SET c = MID( pString, i, 1 );
SELECT c REGEXP '[^a-zA-Z0-9 /t/-/_]' into regx;
IF c="" THEN
SET CleanString=CONCAT(CleanString,' ');
ELSEIF regx =0 THEN
SET CleanString=CONCAT(CleanString,c);
END IF;
SET i = i + 1;
END;
UNTIL i > len END REPEAT;
RETURN CleanString;
END//
DELIMITER ;
---------------------------------------------------------------------------
output
SELECT fn_RemoveSpecialChars('welcone world !#@#@');
Result: welcome world
No comments:
Post a Comment