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

Question

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?

Solution 1

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

Solution 2

You forgot to escape the \ like this:

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

Solution 3

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

Solution 4

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

View more solutions