REPLACE() leaves the line breaks in a MySQL text column
REPLACE(column, '\n', ' ') returns the text with its line breaks still thereShort answer
REPLACE() only matches the exact characters you give it. Line breaks that survive are usually a carriage return left behind by Windows-style breaks, a SQL mode that turns off backslash escapes, the two characters backslash and n stored as text, or a SELECT that never changed the table. Look at the hex of one row first.
Fix it fast
1Find out which cause you have
Look at the bytes of one affected value instead of the way a client draws it. 0A is a line feed, 0D is a carriage return, and 5C6E is a backslash followed by the letter n.
SELECT id, HEX(col) FROM notes ORDER BY id LIMIT 3;2Match what you see, then jump to its fix
- 1
The hex shows 0D0A, and after replacing '\n' a 0D byte is left behindThe text uses carriage return plus line feed, and only one of them was replacedFix - 2
The same statement works on one server and does nothing on another, and no row changesThe SQL mode NO_BACKSLASH_ESCAPES is onFix - 3
The hex of the value shows 5C6E, and the text reads like line one\nline two on a single lineThe column holds a backslash and the letter n, not a real line breakFix - 4
The query output looks clean, but the stored value, read by the app or another query, still has the breaksA SELECT with REPLACE does not change the tableFix
- 1
What does the error look like?
REPLACE(column, '\n', ' ') returns the text with its line breaks still thereThe value still shows on two lines after UPDATE ... SET col = REPLACE(col, '\n', '')
Cause 1 of 4
The text uses carriage return plus line feed, and only one of them was replaced
1Check
In MySQL string literals, \n is a newline (linefeed) and \r is a carriage return. Text that came from a form or file with Windows-style breaks holds both, one after the other.
SELECT id, HEX(col) FROM notes WHERE LOCATE('\r', col) > 0 LIMIT 3;2Fix
Replace the carriage return as well. Do the pair first or each one on its own; the result is the same.
UPDATE notes
SET col = REPLACE(REPLACE(col, '\r', ''), '\n', ' ');3Verify
No row contains either character any more.
SELECT COUNT(*) FROM notes WHERE LOCATE('\r', col) > 0 OR LOCATE('\n', col) > 0;Confirmed by dev.mysql.com
Cause 2 of 4
The SQL mode NO_BACKSLASH_ESCAPES is on
1Check
With this mode enabled, a backslash is an ordinary character, so '\n' is two characters, not a line break. Print the mode of your session.
SELECT @@SESSION.sql_mode;2Fix
Build the characters from their codes with CHAR(), which does not depend on escape handling, or switch the mode off for your session before you run the update.
UPDATE notes
SET col = REPLACE(REPLACE(col, CHAR(13), ''), CHAR(10), ' ');
-- or leave the mode for this session only, then use '\n' as usual
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'NO_BACKSLASH_ESCAPES', '');3Verify
Run the count from the first cause. It returns 0.
SELECT COUNT(*) FROM notes WHERE LOCATE(CHAR(10), col) > 0;Confirmed by dev.mysql.com
Cause 3 of 4
The column holds a backslash and the letter n, not a real line break
1Check
Data that was exported or pasted as escaped text keeps the two characters. A pattern of '\n' looks for a real line feed, so it never matches them. In a MySQL string a literal backslash must be written twice.
SELECT id FROM notes WHERE LOCATE('\\n', col) > 0 LIMIT 3;2Fix
Search for the two-character sequence by doubling the backslash.
UPDATE notes
SET col = REPLACE(col, '\\n', ' ');3Verify
The search for the two-character sequence finds nothing.
SELECT COUNT(*) FROM notes WHERE LOCATE('\\n', col) > 0;Confirmed by dev.mysql.com
Cause 4 of 4
A SELECT with REPLACE does not change the table
1Check
REPLACE() returns a new string. It leaves the stored value as it is. Compare the stored value with the cleaned one for the same row.
SELECT col AS stored, REPLACE(col, '\n', ' ') AS cleaned FROM notes WHERE id = 1;2Fix
Write the result back with an UPDATE, after you have copied the table or tested on a few rows.
CREATE TABLE notes_copy AS SELECT * FROM notes;
UPDATE notes SET col = REPLACE(col, '\n', ' ') WHERE id = 1;3Verify
Read row 1 again from a new connection.
SELECT id, HEX(col) FROM notes WHERE id = 1;Confirmed by dev.mysql.com
How do I stop it coming back?
- Normalize line breaks when the data comes in, in the application, so the table holds one kind only.
- Always check the bytes with HEX() before writing a cleaning UPDATE; the screen hides which character is really there.
- Take a copy of the table, or test on a handful of rows with a WHERE clause, before an UPDATE on the whole column.
- Prefer CHAR(10) and CHAR(13) in scripts that run on servers whose SQL mode you do not control.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix