MySQL 从文本中删除 \n\r 的 sql 查询是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5828364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What would be a sql query to remove \n\r from the text?
提问by user482594
I am using MySQL. My data has a column called text, which uses the TEXT data type.
我正在使用 MySQL。我的数据有一个名为 的列text，它使用 TEXT 数据类型。
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?
此列中的每条记录都有几个换行符。我想用 sql 查询删除所有新行。我怎样才能做到这一点？
回答by Devart
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
回答by Bill Mitchell
The previous suggestions did not work for me. It only seems to work if I had actually typed the \rand \ntext in as text. I found the following to work well -
以前的建议对我不起作用。只有当我实际将\r和\n文本输入为文本时，它似乎才起作用。我发现以下工作正常 -
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.
我还在我的表中创建了一个使用此公式的计算字段。这样我就可以在我的程序中与原始字段内容中断的区域中引用该字段。
回答by Mayur_Vartak
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.
鉴于建议，即REPLACE(REPLACE(DBField, '\n', ''), '\r', '')如果有无形的HTML代码，就像将无法正常工作\n \r。为此，您必须使用字符代码。
Example:
例子：
REPLACE(REPLACE(REPLACE(DBField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')
回答by Tushar
Try this
尝试这个
REPLACE(REPLACE(FIELD, '\n', ''), '\r', '')
回答by Matthias Alleweldt
You can use REPLACE(text,'\n\r',' ')for it.
你可以用REPLACE(text,'\n\r',' ')它。
回答by Jhollman
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:
我已经尝试过这里所说的所有内容，但都不适合我，我的数据库是 IBM Informix，但是我设法解决了这样的问题：
UPDATE personas SET foto_path = SUBSTRING(foto_path FROM 1 FOR LENGTH(foto_path) - 1);
Hope it helps other in similar situation.
希望它可以帮助其他类似情况的人。
回答by EricNo7
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', '')
