How to remove special characters by mysql custom function

Share Me
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Today now in this post i will show you how to remove special characters by using mysql custom function. In mysql custom function is a very pretty and also interesting concept.So now In this posts i want to create a one custom mysql function for remove the special characters from the table field value. So this function through we can also remove the special characters from string in mysql. Now in the following sql query fire in our mysql or mssql database and we can check how it works. So, lets create a function and then see 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`

Read Also : How to add multiple markers in google map

Thanks for read. I hope it help you. For more you can follow us onĀ facebook.

About code chef

My name is Shahriar sagor. I'm a developer. I live in Bangladesh and I love to write tutorials and tips that will help to other Developer's. I am a big fan of PHP, Javascript, JQuery, Laravel, Codeigniter, VueJS, AngularJS and Bootstrap from the early stage.

View all posts by code chef →