mysql set varchar_mySQL set a varchar without the special characters

Method 1:

You can use the REPLACE() method to remove special characters in mysql, don't know if it's very efficient though. But it should work.

Like Below:

SELECT Replace(Replace(product_name,'@',''),'+','') as prod_type

From Table1

Method 2:

If you have All other Special Charcter then go with this (Source)

-- ----------------------------

-- Function structure for `udf_cleanString`

-- ----------------------------

DROP FUNCTION IF EXISTS `udf_cleanString`;

DELIMITER ;;

CREATE FUNCTION `udf_cleanString`(`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) > 31 AND ASCII(c) < 127 THEN

SET out_str = CONCAT(out_str, c);

END IF;

SET pointer = pointer + 1;

END WHILE;

END IF;

RETURN out_str;

END

;;

DELIMITER ;

After that just call the function as follows:

SELECT product_name, udf_cleanString(product_name) AS 'product_Type'

FROM table1;