How to remove all non-alphanumeric characters from a string in MySQL?

Non-alphanumeric characters are as follows −

 @,!,#,&,(),?, / 

There is no inbuilt function to remove non-alphanumeric characters from a string in MySQL. Therefore, we create a function which removes all non-alphanumeric characters. The function declaration and definition is as follows.

 mysql> delimiter // mysql> CREATE FUNCTION RemoveNonAlphaNumeric( s CHAR(255) ) RETURNS CHAR(255) DETERMINISTIC -> BEGIN -> DECLARE var1, length SMALLINT DEFAULT 1; -> DECLARE result CHAR(255) DEFAULT ''; -> DECLARE ch CHAR(1); -> SET length = CHAR_LENGTH( s ); -> REPEAT -> BEGIN -> SET ch = MID( s, var1, 1 ); -> IF ch REGEXP '[[:alnum:]]' THEN -> SET result =CONCAT(result ,ch); -> END IF; -> SET var1 = var1 + 1; -> END; -> UNTIL var1 >length END REPEAT; -> RETURN result ; -> END // Query OK, 0 rows affected (0.10 sec) 

The function named ?RemoveNonAlphaNumeric' removes all non-alphanumeric characters from a string. To check, we will now call the user-defined function.

 mysql>delimiter ; mysql> select 'My Email id is test@123!',RemoveNonAlphaNumeric('My Email id is test@123!'); 

The following is the output that displays the successful removal of alphanumeric characters using the function "RemoveNonAlphaNumeric".

 +--------------------------+---------------------------------------------------+ | My Email id is test@123! | removeNonAlphaNumeric('My Email id is test@123!') | +--------------------------+---------------------------------------------------+ | My Email id is test@123! | MyEmailidistest123 | +--------------------------+---------------------------------------------------+ 1 row in set (0.15 sec) 

In this string (MyEmailidistest123 ),there are no @ and ! symbols now, which means the function is working correctly.