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
You can match a newline character using \n, not \\n.
Code:
UPDATE aboutme
SET abouttext=REPLACE(abouttext,'\n','')
WHERE userid='5099a95cd944b8.22468149';
SELECT, such as SELECT * FROM aboutme WHERE abouttext LIKE '%\n%'? You may need to find the correct control character to match. See e.g. the code listed on this page: dev.mysql.com/doc/refman/5.5/en/regexp.htmlIf \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.
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','');
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
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.
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')
Try something like that:
REPLACE(REPLACE(text, CHAR(92), '#'), '#n', ' ')
The better way i found was:
UPDATE aboutme
SET abouttext=REPLACE(REPLACE(abouttext,'\r',''),'\n','')
WHERE userid='5099a95cd944b8.22468149';
abouttextthe column you want to store the replacement in? Are you sure you don't meanquestion=REPLACE(question,'\\n','')?\\n