I have used single column to store multiple comments . In that column i have to store every comments in new line so that i can able to differentiate the comments. I have tried CHAR(13) and CHAR(13) + CHAR(10) between two strings . But it's not working.I shown the records in single line.
Tried code:
DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'
SELECT @text
Please suggest the solution.
You can use PRINT statement instead of SELECT statement to achieve what you want.
For example, you can use any of the followings:
PRINT 'This is line 1.' + CHAR(13) + 'This is line 2.'
Or
PRINT 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'
Or
PRINT CONCAT(N'This is line 1.', 0xd000a, N'This is line 2.')
UPDATE: According to this forum,
You can not see char(13) in SSMS in the Grid format. This character is there and you can see it if you output the result into report, into text, into Excel. But in SSMS grid you can not see this character.
You can change settings from "Results to Grid" to "Results to Text" from menu using the following steps:
Query -> Results to -> Results to Text
Then you will be able to view line break between two strings using any of the followings
SELECT 'This is line 1.' + CHAR(13) + 'This is line 2.'
Or
SELECT 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'
Or
SELECT CONCAT(N'This is line 1.', 0xd000a, N'This is line 2.')
another way
select concat(N'This is line 1.', 0xd000a, N'This is line 2.')
or
select 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'