insert carriage return in string

Thirston Third 21 Reputation points

How do you add a carriage return in a string something like this but it doesn't work?
select 'first line ' + char(13) + char(10) +
'second line '

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,462 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points

    It does work, you just would not be able to see it if you view results in a grid in SSMS. If you change the output of the query to a text or a file you'll see separate lines. Menu Query/Results to.

  2. Thirston Third 21 Reputation points

    I'm trying to return 1 record in grid view in SSMS. When you copy and paste it into a new query window it displays on 2 lines.

  3. Erland Sommarskog 93,141 Reputation points

    By default, when you copy from a grid, SSMS will replace CR+LF with spaces. This is what you want when you copy to Excel.

    But there is a setting for this under Tools->Options.


    0 comments No comments

  4. Thirston Third 21 Reputation points

    This fixed it. You need to restart sql server.
    tools/options/query results/sql server/results to grid/check retain CR/LF on copy or save