How to insert a line break in a SQL Server VARCHAR/NVARCHAR string

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

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 2

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 3

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 4

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 interresting for, say, cvs files, so beware.

Anyow, 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 lengt of the varchar field you are inserting to..)

Answer 5

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'"

Regards,
Frank

Answer 6

I ran across this problem in a medical web app that needs to display individual "records" as lines in an [input type="text"/] element. Inserting CRLFs at the end of each line is exactly the ticket, in this case.

THANKS to the posters here (and to StackOverflow!), who saved me a bunch of time!

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.

--Carl

---

    /// <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("");
        }
    }