MS-SQL and embedded new lines

Handling SQL New Lines

There are times when using MS-SQL, that you have to deal with embedded new lines, or carriage returns.  It could be a matter of removing an embedded new line from a column in a MS-SQL table,  It could also be wanting to insert a new line in a column.  Such would be the case with a description that is being pulled from MS-SQL for display on a web page.

With new lines, there are a couple of ways to deal with them.  The first is to use backslash-n ("\n"), which specifically is the new line.  So if you save a field such as "This is \na new line" into a column, it will display as:

     This is
     a new line

When it is pulled from MS-SQL.  If you want to do the reverse, and remove a new line from a field, you would use replace(field, "\n", "").  The result of this would be to remove all new lines in "field".

The other, more general method of dealing with new lines, is using the character (CHAR) function of MS-SQL.  The trick here, is that (at least as far as Windows is concerned), a new line is a carriage-return, followed by a line-feed.  So, that would equate to "CHAR(13)+CHAR(10)", when using this method.


Leave a Reply