- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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.
- Related Articles
- PHP program to remove non-alphanumeric characters from string
- How to remove non-alphanumeric characters in PHP stirng?
- Remove all non-alphabetical characters of a String in Java?
- JavaScript Remove non-duplicate characters from string
- Removing all non-alphabetic characters from a string in JavaScript
- Python - Check If All the Characters in a String Are Alphanumeric?
- How to remove non-ASCII characters from strings
- How to Remove Characters from a String in Arduino?
- 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 certain characters from a string in C++?
- How to remove specific characters from a string in Python?
- Remove all characters of first string from second JavaScript
- Write a Regular Expression to remove all special characters from a JavaScript String?
- How to remove special characters from a database field in MySQL?

Advertisements