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.
Updated on: 30-Jul-2019
2K+ Views
Advertisements