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.