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?

Answer 1

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

Answer 2

You forgot to escape the \ like this:

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