CODEWITHSUNDEEP
c#sqlsql-serverdatabase
Kylar
Kylar

Reputation: 9334

Convert "\n" to actual newline in SQL Server

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.

Upvotes: 8

Views: 19777

Answers (3)

JBrooks
JBrooks

Reputation: 10013

This:

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

will produce:

Line 1
Line 2

Upvotes: 3

Mayo
Mayo

Reputation: 10782

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))

Upvotes: 1

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195982

this should do the trick

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

Upvotes: 13

Related Questions