insert carriage return in string

Thirston Third 21 Reputation points
2022-03-31T13:11:27.787+00:00

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 '

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

4 answers

Sort by: Most helpful
  1. Naomi Nosonovsky 7,886 Reputation points
    2022-03-31T13:15:32.963+00:00

    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
    2022-03-31T15:14:46.257+00:00

    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 112.7K Reputation points MVP
    2022-03-31T22:06:34.937+00:00

    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.

    188897-image.png

    0 comments No comments

  4. Thirston Third 21 Reputation points
    2022-03-31T22:16:13.373+00:00

    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


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.