Query to Remove Text in MySQL

To remove
\r from the text, you need to use REPLACE command. The syntax is as follows −

UPDATE yourTableName SET yourColumnName=REPLACE(yourColumnName,’\r
’,’ ‘);

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table removeDemo
-> (
-> Id int NOT NULL AUTO_INCREMENT,
-> Name text,
-> PRIMARY KEY(Id)
-> );
Query OK, 0 rows affected (0.79 sec)

Now insert some records in the table using insert command. The query is as follows −

mysql> insert into removeDemo(Name) values('John\r
SmithCarol');
Query OK, 1 row affected (0.13 sec)
mysql> insert into removeDemo(Name) values('LarryMike\r
Sam');
Query OK, 1 row affected (0.34 sec)
mysql> insert into removeDemo(Name) values('David\r
BobJames');
Query OK, 1 row affected (0.10 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from removeDemo;

The following is the output in a format that includes \r
, therefore the output isn’t looking properly formatted −

+----+------------------+
| Id | Name             |
+----+------------------+
| 1  | John SmithCarol  |
| 2  | LarryMike Sam    |
| 3  | David BobJames   |
+----+------------------+
3 rows in set (0.00 sec)

Here is the query to remove \r
from text −

mysql> update removeDemo set Name=replace(Name,'\r
','');
Query OK, 3 rows affected (0.12 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Now check the table records once again. The query is as follows −

mysql> select *from removeDemo;

The following is the output −

+----+----------------+
| Id | Name           |
+----+----------------+
| 1 | JohnSmithCarol  |
| 2 | LarryMikeSam    |
| 3 | DavidBobJames   |
+----+----------------+
3 rows in set (0.00 sec)