- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
Advertisements