16

I am using MySQL. My data has a column called text, which uses the TEXT data type.

There are several newlines for each record in this column. I want to remove all new lines with a sql query. How can I do that?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
user482594
  • 16,878
  • 21
  • 72
  • 108

7 Answers7

24

Try this one -

CREATE TABLE table1(column1 TEXT);
INSERT INTO table1 VALUES ('text1\r\ntext2
text3');

SELECT * FROM table1;
--------
text1
text2
text3

UPDATE table1 SET column1 = REPLACE(column1, '\r\n', '');
SELECT * FROM table1;
--------
text1text2text3
Devart
  • 119,203
  • 23
  • 166
  • 186
15

The previous suggestions did not work for me. It only seems to work if I had actually typed the \r and \n text in as text. I found the following to work well -

replace(replace([MyFieldName],char(13),''),char(10),'')

I also created a calculated field in my table which uses this formula. That way I can just reference that field in areas of my program that were breaking with the original field contents.

bensiu
  • 24,660
  • 56
  • 77
  • 117
Bill Mitchell
  • 161
  • 1
  • 4
10

Given suggestion i.e. REPLACE(REPLACE(DBField, '\n', ''), '\r', '') won't work if there are invisible html code like \n \r. For that you have to use char code.

Example: 

REPLACE(REPLACE(REPLACE(DBField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')
Tom
  • 1,387
  • 3
  • 19
  • 30
Mayur_Vartak
  • 151
  • 2
  • 13
8

Try this

REPLACE(REPLACE(FIELD, '\n', ''), '\r', '')
Tushar
  • 1,242
  • 1
  • 8
  • 19
  • 3
    Agreed. This one guarantees those \n and \r characters (in any order and combination) will not escape from the replacement. `UPDATE table1 SET column1 = REPLACE(REPLACE(column1, '\n', ''), '\r', '');` – Ken Pega Nov 27 '14 at 04:12
1

i've tried all said here but neither worked for me, my DB is IBM Informix, however i managed to solve the problem like this:

UPDATE personas SET foto_path = SUBSTRING(foto_path FROM 1 FOR LENGTH(foto_path) - 1);

Hope it helps other in similar situation.

Jhollman
  • 2,093
  • 24
  • 19
0

The above version with single backslash cleared up some for me but also had to run this to clear up the rest.

REPLACE(REPLACE(FIELD, '\\n', ''), '\\r', '')
EricNo7
  • 31
  • 5
0

You can use REPLACE(text,'\n\r',' ') for it.

Matthias Alleweldt
  • 2,453
  • 17
  • 16