Remove Special Characters in mysql select Statement.

Select Statement:
SELECT  Country FROM country

Remove Special Character Function:
CREATE FUNCTION RemoveSpecialChar(`Input_str` text)
  RETURNS text CHARSET utf8
BEGIN
  DECLARE output_str text DEFAULT ''; 
  DECLARE c text DEFAULT ''; 
  DECLARE pointer INT DEFAULT 1; 

  IF ISNULL(Input_str) THEN
        RETURN NULL; 
  ELSE
    WHILE pointer <= LENGTH(Input_str) DO 
      SET c = MID(Input_str, pointer, 1); 
      IF (ASCII(c) NOT IN(33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,63,126,58,59,92)) THEN
          SET output_str = CONCAT(output_str, c); 
      ELSE
          SET output_str = CONCAT(output_str, ' ');   
      END IF; 
      SET pointer = pointer + 1; 
    END WHILE; 
  END IF; 
  RETURN output_str; 
END

RESULT:

SELECT  RemoveSpecialChar(country) FROM country

Post a Comment

0 Comments