Menu
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.
520
Remove and Replace Carriage Returns and Line Breaks in SQL Using SQL to remove a line feed or carriage return means using the CHAR function. A line feed is CHAR(10); a carriage return is CHAR(13).
The "N" prefix stands for National Language in the SQL-92 standard, and is used for representing Unicode characters.
-- Using both \r\n SELECT 'First line. \r\nSecond Line. ' AS 'New Line'; -- Using both \n SELECT 'First line.
this should do the trick
UPDATE
<tablename>
SET
<fieldname> = replace(<fieldname>,'\n',char(13)+char(10)),
<otherfieldname> = replace(< otherfieldname >,'\n',char(13)+char(10))
This:
print replace('Line 1\nLine 2','\n',char(13))
will produce:
Line 1
Line 2
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
