Convert "\n" to actual newline in SQL Server

Question

I have a bunch of varchar(255) and varchar(max) fields in a table in MS SQL Server.

These are generally formatted messages (email and other). Most of the fields have the actual characters "\n", but actually need a newline character. I don't need to worry about new data going forward, but don't know how to fix the stuff that's currently in the DB.

I'm mostly a programmer, not a SQL/DB Guy, so any pointers on how to approach fixing this, or resources to get me started would be appreciated.

Solution 1

this should do the trick

UPDATE
    <tablename> 
SET
    <fieldname> = replace(<fieldname>,'\n',char(13)+char(10)),
    <otherfieldname> = replace(< otherfieldname >,'\n',char(13)+char(10))

Solution 2

This:

print replace('Line 1\nLine 2','\n',char(13))

will produce:

Line 1
Line 2

Solution 3

I believe you are looking for char(13)...

select 'hi' + char(13) + 'there'

You can replace \n in select statements...

select replace('hi\nthere', '\n', char(13))

Or you can do an update...

update table set str = replace(str, '\n', char(13))