我需要在 mysql 字段中查找和替换 \n
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9362213/
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
I need to find and replace \n in a mysql field
提问by bjohnb
i've got data like this:
我有这样的数据：
1 street\n2street\nmycity\nmytown
1 street\n2street\nmycity\nmytown
What i want to do is replace \n with char(10) as i need a real linebreak in the db field.
我想要做的是用 char(10) 替换 \n ，因为我需要在 db 字段中真正换行。
I've got:
我有：
UPDATE data set `value` = REPLACE(`value`,'\n', char(10)) WHERE `key`='shipping_address';
But that is not working.
但这行不通。
Can anyone help please?
有人可以帮忙吗？
回答by Nugget
UPDATE data set `value` = REPLACE(`value`,'\n', CHAR(10)) WHERE `key`='shipping_address';
回答by aF.
You forgot to escape the \like this:
你忘了\像这样逃避：
UPDATE data set `value` = REPLACE(`value`,'\n', char(10)) WHERE `key`='shipping_address';
回答by Michael Berkowski
Double the backslash to escape it and treat it as a literal:
将反斜杠加倍以将其转义并将其视为文字：
UPDATE data set `value` = REPLACE(`value`,'\n', char(10)) WHERE `key`='shipping_address';
回答by Daniel
New line character is either '\n' (Line Feed) or '\r' (Carriage return) individually, or CR followed by LF '\r\n' depending on the os.
换行符是单独的 '\n'（换行）或 '\r'（回车），或者 CR 后跟 LF '\r\n' 取决于操作系统。
UPDATE data set `value` = REPLACE(`value`,'\r' or '\n' or '\r\n', char(10)) WHERE `key`='shipping_address';
回答by lauralacarra
My case: It didn't work because I thought that it had \n but it had \r
我的情况：它没有用，因为我认为它有 \n 但它有 \r
update [Table] set [column]=replace(convert([column] using utf8) ,'\r','');
回答by Sweet Chilly Philly
I wanted to actually replace line endings in my columns. This worked for me:
我想真正替换我的列中的行尾。这对我有用：
UPDATE `table`
SET
`column` = REPLACE(`column`, char(13), '');
char(13) is carriage return and char(10) is line feed. You may have either.
char(13) 是回车符，char(10) 是换行符。你可能有。
