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 special characters from a database field in MySQL?
You can remove special characters from a database field using REPLACE() function. The special characters are double quotes (“ “), Number sign (#), dollar sign($), percent (%) etc.
The syntax is as follows to remove special characters from a database field.
UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName,’yourSpecialCharacters’,’’);
To understand the above syntax, let us create a table. The query to create a table is as follows:
mysql> create table RemoveSpecialCharacterDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> PRIMARY Key(Id) -> ); Query OK, 0 rows affected (0.59 sec)
Insert some records in the table using insert command. The query is as follows:
mysql> insert into RemoveSpecialCharacterDemo(Name) values('$John');
Query OK, 1 row affected (0.29 sec)
mysql> insert into RemoveSpecialCharacterDemo(Name) values('$Carol');
Query OK, 1 row affected (0.16 sec)
mysql> insert into RemoveSpecialCharacterDemo(Name) values('$Mike');
Query OK, 1 row affected (0.17 sec)
mysql> insert into RemoveSpecialCharacterDemo(Name) values('$Sam');
Query OK, 1 row affected (0.14 sec)
mysql> insert into RemoveSpecialCharacterDemo(Name) values('$Dav$id$');
Query OK, 1 row affected (0.17 sec)
mysql> insert into RemoveSpecialCharacterDemo(Name) values('Robert$');
Query OK, 1 row affected (0.30 sec)
mysql> insert into RemoveSpecialCharacterDemo(Name) values('J$ames$');
Query OK, 1 row affected (0.13 sec)
mysql> insert into RemoveSpecialCharacterDemo(Name) values('Max$well$');
Query OK, 1 row affected (0.27 sec)Display all records from the table using select statement. The query is as follows:
mysql> select *from RemoveSpecialCharacterDemo;
The following is the output:
+----+-----------+ | Id | Name | +----+-----------+ | 1 | $John | | 2 | $Carol | | 3 | $Mike | | 4 | $Sam | | 5 | $Dav$id$ | | 6 | Robert$ | | 7 | J$ames$ | | 8 | Max$well$ | +----+-----------+ 8 rows in set (0.00 sec)
Here is the query to remove special characters from a database field using REPLACE():
mysql> update RemoveSpecialCharacterDemo -> set Name=replace(Name,'$',''); Query OK, 8 rows affected (0.22 sec) Rows matched: 8 Changed: 8 Warnings: 0
Check the table records once again. The query to display all records is as follows:
mysql> select *from RemoveSpecialCharacterDemo;
The following is the output:
+----+---------+ | Id | Name | +----+---------+ | 1 | John | | 2 | Carol | | 3 | Mike | | 4 | Sam | | 5 | David | | 6 | Robert | | 7 | James | | 8 | Maxwell | +----+---------+ 8 rows in set (0.00 sec)
Look at the sample output, the special character $ has been removed completely from the table.
18K+ Views
