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

User · Answer

Run this in SSMS  it shows how line breaks in the SQL itself become part of string values that span lines    PRINT  Line 1 Line 2 Line 3  PRINT     PRINT  How long is a blank line feed   PRINT LEN      PRINT     PRINT  What are the ASCII values   PRINT ASCII SUBSTRING     1 1   PRINT ASCII SUBSTRING     2 1     Result   Line 1 Line 2 Line 3    How long is a blank line feed  2    What are the ASCII values  13 10    Or if you d rather specify your string on one line  almost   you could employ REPLACE   like this  optionally use CHAR 13  CHAR 10  as the replacement     PRINT REPLACE  Line 1 Line 2 Line 3

User · Answer

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        lt summary gt      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       lt  summary gt       lt param name  sNewLine  gt Single text line to be prepended to existing text lt  param gt       lt param name  sOrigLines  gt Current text value  assumed to be CRLF-delimited lt  param gt       lt param name  iMaxLen  gt Integer field length lt  param gt       lt returns gt String  SQL string expression suitable for INSERT UPDATE operations   Empty on error  lt  returns gt  private string PrependCommentLine string sNewLine  String sOrigLines  int iMaxLen        String fn   MethodBase GetCurrentMethod   Name       try               String    line array   sOrigLines Split   r n  ToCharArray             List lt string gt  orig lines   new List lt string gt             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  gt   iMaxLen  break                    stop appending if we re already over             if   cum length orig line Length 2  gt  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

User · Answer

I got here because I was concerned that cr-lfs that I specified in C  strings were not being shown in SQl Server Management Studio query responses   It turns out  they are there  but are not being displayed   To  see  the cr-lfs  use the print statement like   declare  tmp varchar 500      select  tmp   msgbody from emailssentlog where id 6769  print  tmp

User · Answer

In some special cases you may find this useful  e g  rendering cell-content in MS Report   example   select   from    values       use STAGING          go          EXEC sp MSforeachtable   command1   select           as tablename count 1  as anzahl from    having count 1    0       as t  Copy and execute this statement   go

User · Answer

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

User · Answer

I d say   concat  This is line 1    0xd0a   This is line 2      or   concat N This is line 1    0xd000a  N This is line 2

User · Answer

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

User · Answer

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 advise using 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

User · Answer

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

User · Answer

All of these options work depending on your situation  but you may not see any of them work if you re using SSMS     as mentioned in some comments SSMS hides CR LFs   So rather than driving yourself round the bend  Check this setting in   Tools   Options

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to line-breaks