Replacing \r\n (Newline Characters) in MSSQL

While working on replacing an HTML textarea-based board with TinyMCE, I ran into an annoying issue because the newline characters were stored in the DB as CRLF(\r\n).

If you want to replace CRLF in MSSQL, you need to REPLACE CHAR(13) and CHAR(10).

The QUERY below is an example of using REPLACE to change CRLF to a <br /> tag.

update tbl_board
set contents = replace(replace(REPLACE(contents, CHAR(13), ' '), char(10), ' '), '  ' , '<br />')

Works like a charm!

• Reference: What is the newline character in an HTML textarea?