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.

Chandu yadav

Published on 23-Nov-2018 05:08:51

• Related Questions & Answers
• PHP program to remove non-alphanumeric characters from string
• How to remove non-alphanumeric characters in PHP stirng?
• JavaScript Remove non-duplicate characters from string
• Remove all non-alphabetical characters of a String in Java?
• Removing all non-alphabetic characters from a string in JavaScript
• How to remove non-ASCII characters from strings
• How to Remove Characters from a String in Arduino?
• Remove all characters of first string from second JavaScript
• How to remove all special characters, punctuation and spaces from a string in Python?
• MySQL Query to remove all characters after last comma in string?
• How to remove specific characters from a string in Python?
• How to remove certain characters from a string in C++?
• Write a Regular Expression to remove all special characters from a JavaScript String?
• How to remove non-word characters in JavaScript?
• How to trim down non printable characters from a string in Python?