I need to find and replace \\n in a mysql field

问题:

i've got data like this:

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.

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?

回答1:

UPDATE data set `value` = REPLACE(`value`,'\\n', CHAR(10)) WHERE `key`='shipping_address';

回答2:

You forgot to escape the \ like this:

UPDATE data set `value` = REPLACE(`value`,'\\n', char(10)) WHERE `key`='shipping_address';

回答3:

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';

回答4:

New line character is either '\n' (Line Feed) or '\r' (Carriage return) individually, or CR followed by LF '\r\n' depending on the os.

UPDATE data set `value` = REPLACE(`value`,'\r' or '\n' or '\r\n', char(10)) WHERE `key`='shipping_address';

回答5:

My case: It didn't work because I thought that it had \n but it had \r

update [Table] set [column]=replace(convert([column] using utf8) ,'\r','');

回答6:

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.