Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

REPLACE() leaves the line breaks in a MySQL text column

ErrorREPLACE(column, '\n', ' ') returns the text with its line breaks still there

Short 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.

  • Applies to MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2The same statement works on one server and does nothing on another, and no row changesThe SQL mode NO_BACKSLASH_ESCAPES is onFix
    3. 3The 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. 4The 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

What does the error look like?

  • REPLACE(column, '\n', ' ') returns the text with its line breaks still there
  • The 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.