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.
insert carriage return in string
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 '
4 answers
Sort by: Most helpful
-
-
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.
-
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.
-
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