How to remove special characters by mysql custom function.

By Hardik Savani | March 10, 2016 | | 49113 Viewer | Category : PHP MySql SQL MSSQL


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`



Hardik Savani
My name is Hardik Savani. I'm a full-stack developer, entrepreneur and owner of Aatman Infotech. I live in India and I love to write tutorials and tips that can help to other artisan. I am a big fan of PHP, Javascript, JQuery, Laravel, Codeigniter, VueJS, AngularJS and Bootstrap from the early stage.
Follow Me: Github Twitter
***Do you want me hire for your Project Work? Then Contact US.