CODEWITHSUNDEEP
mysqlsql
user482594
user482594

Reputation: 17486

What would be a sql query to remove \n\r from the text?

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?

Upvotes: 16

Views: 46986

Answers (7)

EricNo7
EricNo7

Reputation: 41

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', '')

Upvotes: 0

Mayur_Vartak
Mayur_Vartak

Reputation: 161

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), '')

Upvotes: 11

BlueMystic
BlueMystic

Reputation: 2287

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.

Upvotes: 1

Bill Mitchell
Bill Mitchell

Reputation: 171

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.

Upvotes: 16

Devart
Devart

Reputation: 121902

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

Upvotes: 24

Matthias Alleweldt
Matthias Alleweldt

Reputation: 2453

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

Upvotes: 0

Tushar
Tushar

Reputation: 1252

Try this

REPLACE(REPLACE(FIELD, '\n', ''), '\r', '')

Upvotes: 8

Related Questions