CODEWITHSUNDEEP
mysqlsql
Pit Digger
Pit Digger

Reputation: 9780

REPLACE new line character in MYSql not working

I executed following query and for some reason its not replacing new line character in database . It says Rows matched 1 but no change . What can be wrong ?

mysql> UPDATE aboutme SET abouttext=REPLACE(abouttext,'\\n','') WHERE userid='5099a95cd944b8.22468149';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Upvotes: 42

Views: 93637

Answers (8)

El Toni
El Toni

Reputation: 1

The better way i found was:

UPDATE aboutme 
 SET abouttext=REPLACE(REPLACE(abouttext,'\r',''),'\n','') 
 WHERE userid='5099a95cd944b8.22468149';

Upvotes: 0

algorun
algorun

Reputation: 87

The only thing that worked for me was using this :

UPDATE aboutme SET abouttext = REPLACE(REPLACE(abouttext, CHAR(13), ''), CHAR(10), '') WHERE userid='5099a95cd944b8.22468149';

Using Ryan' solution was not updating anything but was messing with MySQL output formatting

Upvotes: 4

Lipa
Lipa

Reputation: 51

Try something like that:

REPLACE(REPLACE(text, CHAR(92), '#'), '#n', ' ')

Upvotes: 0

TheTechGuy
TheTechGuy

Reputation: 17354

If \n does not work as in my case, the following worked \r\n

UPDATE aboutme 
SET abouttext=REPLACE(abouttext,'\r\n','') 
WHERE userid='5099a95cd944b8.22468149';

In my case it has been a web application.

Upvotes: 28

Do Hoa Vinh
Do Hoa Vinh

Reputation: 356

the REPLACE function is case sensitive, i think it belongs MySql server version

description=REPLACE(description, 'Videosite', 'video.5la.net') is different result with description=REPLACE(description, 'VideoSite', 'video.5la.net')

Upvotes: 0

lauralacarra
lauralacarra

Reputation: 620

You think that it contains \n, but it has \r.

update [Table] set [column]=replace(convert([column] using utf8) ,'\r','');

In your case:

update aboutme set abouttext=replace(convert(abouttext using utf8) ,'\r','');

Upvotes: 10

jcho360
jcho360

Reputation: 3759

this is what happening

mysql> mysql> select * from t1 limit 3;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
3 rows in set (0.00 sec)

mysql> update t1 set first_name=replace(first_name,'abc','') where first_name='ed';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 10  Changed: 0  Warnings: 0

mysql> select * from t1 limit 3;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
3 rows in set (0.00 sec)


mysql> update t1 set first_name=replace(first_name,'ED','EDD') where first_name='ed';
Query OK, 10 rows affected (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 0

mysql> select * from t1 limit 3;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
|        3 | EDD        | CHASE     | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

3 rows in set (0.00 sec)

What I meant is that the where condition it's working that's why you have 'rows matched: 1' but your replace don't find \\n to replace it, that's why changed: 0 so check your table data.

Upvotes: 0

Ryan
Ryan

Reputation: 28177

You can match a newline character using \n, not \\n.

Code:

 UPDATE aboutme 
 SET abouttext=REPLACE(abouttext,'\n','') 
 WHERE userid='5099a95cd944b8.22468149';

Upvotes: 61

Related Questions