How to insert a line break in a SQL Server VARCHAR

Question

I didn't see any similar questions asked on this topic, and I had to research this for something I'm working on right now. Thought I would post the answer for it in case anyone else had the same question.

Answer 1

char(13) is CR. For DOS-/Windows-style CRLF linebreaks, you want char(13)+char(10), like:

'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'

Answer 2

This is always cool, because when you get exported lists from, say Oracle, then you get records spanning several lines, which in turn can be interesting for, say, cvs files, so beware.

Anyhow, Rob's answer is good, but I would advice to use something else than @, try a few more, like §§@@§§ or something, so it will have a chance for some uniqueness. (But still, remember the length of the varchar/nvarchar field you are inserting into..)

Answer 3

I found the answer here: http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/

You just concatenate the string and insert a CHAR(13) where you want your line break.

Example:

DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'
SELECT @text

This prints out the following:

This is line 1.
This is line 2.

Answer 4

Another way to do this is as such:

INSERT CRLF SELECT 'fox 
jumped'

That is, simply inserting a line break in your query while writing it will add the like break to the database. This works in SQL server Management studio and Query Analyzer. I believe this will also work in C# if you use the @ sign on strings.

string str = @"INSERT CRLF SELECT 'fox 
    jumped'"

Answer 5

Following a Google...

Taking the code from the website:

CREATE TABLE CRLF
    (
        col1 VARCHAR(1000)
    )

INSERT CRLF SELECT 'The quick brown@'
INSERT CRLF SELECT 'fox @jumped'
INSERT CRLF SELECT '@over the '
INSERT CRLF SELECT 'log@'

SELECT col1 FROM CRLF

Returns:

col1
-----------------
The quick brown@
fox @jumped
@over the
log@

(4 row(s) affected)


UPDATE CRLF
SET col1 = REPLACE(col1, '@', CHAR(13))

Looks like it can be done by replacing a placeholder with CHAR(13)

Good question, never done it myself :)

Answer 6

Here's a C# function that prepends a text line to an existing text blob, delimited by CRLFs, and returns a T-SQL expression suitable for INSERT or UPDATE operations. It's got some of our proprietary error handling in it, but once you rip that out, it may be helpful -- I hope so.

/// <summary>
/// Generate a SQL string value expression suitable for INSERT/UPDATE operations that prepends
/// the specified line to an existing block of text, assumed to have \r\n delimiters, and
/// truncate at a maximum length.
/// </summary>
/// <param name="sNewLine">Single text line to be prepended to existing text</param>
/// <param name="sOrigLines">Current text value; assumed to be CRLF-delimited</param>
/// <param name="iMaxLen">Integer field length</param>
/// <returns>String: SQL string expression suitable for INSERT/UPDATE operations.  Empty on error.</returns>
private string PrependCommentLine(string sNewLine, String sOrigLines, int iMaxLen)
{
    String fn = MethodBase.GetCurrentMethod().Name;

    try
    {
        String [] line_array = sOrigLines.Split("\r\n".ToCharArray());
        List<string> orig_lines = new List<string>();
        foreach(String orig_line in line_array) 
        { 
            if (!String.IsNullOrEmpty(orig_line))  
            {  
                orig_lines.Add(orig_line);    
            }
        } // end foreach(original line)

        String final_comments = "'" + sNewLine + "' + CHAR(13) + CHAR(10) ";
        int cum_length = sNewLine.Length + 2;
        foreach(String orig_line in orig_lines)
        {
            String curline = orig_line;
            if (cum_length >= iMaxLen) break;                // stop appending if we're already over
            if ((cum_length+orig_line.Length+2)>=iMaxLen)    // If this one will push us over, truncate and warn:
            {
                Util.HandleAppErr(this, fn, "Truncating comments: " + orig_line);
                curline = orig_line.Substring(0, iMaxLen - (cum_length + 3));
            }
            final_comments += " + '" + curline + "' + CHAR(13) + CHAR(10) \r\n";
            cum_length += orig_line.Length + 2;
        } // end foreach(second pass on original lines)

        return(final_comments);


    } // end main try()
    catch(Exception exc)
    {
        Util.HandleExc(this,fn,exc);
        return("");
    }
}