MySQL Query to Remove Special Characters from String
Mysql custom fucntion is a very prety and intresting concept. In this posts i want to create one custom mysql function for remove special characters from table field value. this function through you can also remove special characters from string in mysql. in following sql query fire in your mysql or mssql database and check how it works. So, lets create function and how to use in select statement in sql query.
Create removeSpacialChar function:
CREATE FUNCTION `removeSpacialChar`(`in_str` varchar(4096)) RETURNS varchar(4096) CHARSET utf8
BEGIN
DECLARE out_str VARCHAR(4096) DEFAULT '';
DECLARE c VARCHAR(4096) DEFAULT '';
DECLARE pointer INT DEFAULT 1;
IF ISNULL(in_str) THEN
RETURN NULL;
ELSE
WHILE pointer <= LENGTH(in_str) DO
SET c = MID(in_str, pointer, 1);
IF (ASCII(c) >= 48 AND ASCII(c) <= 57) OR (ASCII(c) >= 65 AND ASCII(c) <= 90) OR (ASCII(c) >= 97 AND ASCII(c) <= 122) THEN
SET out_str = CONCAT(out_str, c);
ELSE
SET out_str = CONCAT(out_str, ' ');
END IF;
SET pointer = pointer + 1;
END WHILE;
END IF;
RETURN out_str;
END
Use With Select Query:
SELECT removeSpacialChar(users.name) FROM `users`
I hope it can help you...