New line in sql server

Question

I am using an procedure to Select First-name and Last-name

declare 
    @firstName nvarchar(50),
    @lastName nvarchar(50),
    @text nvarchar(MAX)

SELECT @text = 'First Name : ' + @firstName + ' Second Name : ' + @lastName

The @text Value will be sent to my mail But the Firstname and Lastname comes in single line. i just need to show the Lastname in Second line

O/P First Name : Taylor Last Name : Swift ,
I need the output like this below format

First Name : Taylor 
Last Name : Swift

Devart · Accepted Answer · 2013-08-30T08:45:06.987

11

Try to use CHAR(13) -

DECLARE 
      @firstName NVARCHAR(50) = '11'
    , @lastName NVARCHAR(50) = '22'
    , @text NVARCHAR(MAX) 

SELECT @text = 
    'First Name : ' + @firstName + 
    CHAR(13) + --<--
    'Second Name : ' + @lastName

SELECT @text

Output -

First Name : 11
Second Name : 22

edited Aug 30 '13 at 08:45

answered Aug 30 '13 at 08:39

Devart

115,199
22
161
180

Using only CHAR(13) gives strange result when writing result to console. Lines seem to overwrite each other. Adding CHAR(10) (=line feed) after the CHAR(13) fixes this problem. – Bruno Apr 20 '16 at 09:19

score 10 · Answer 2 · edited Aug 30 '13 at 08:53

10

You may use

CHAR(13) + CHAR(10)

edited Aug 30 '13 at 08:53

podiluska

50,144
7
94
100

answered Aug 30 '13 at 08:38

Giannis Paraskevopoulos

17,836
1
48
66

this does not work for some reason in online versions i.e. Hackerrank – David Andrei Ned Jun 26 '17 at 19:09

score 6 · Answer 3 · edited Aug 30 '13 at 08:59

6

Try this

DECLARE @firstName NVARCHAR(50)
    ,   @lastName NVARCHAR(50)
    ,   @text NVARCHAR(MAX)

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

SELECT @text = 'First Name : ' + @firstName + @NewLineChar + 'Second Name : ' + @lastName
PRINT @Text

edited Aug 30 '13 at 08:59

Devart

115,199
22
161
180

answered Aug 30 '13 at 08:55

AB Vyas

2,457
6
26
43

score 2 · Answer 4 · edited Aug 30 '13 at 09:45

2

CHAR(13) will show the text in new line, when you switch to Result to text.

edited Aug 30 '13 at 09:45

Devart

115,199
22
161
180

answered Aug 30 '13 at 09:07

Gemini

89
1

score 1 · Answer 5 · answered Aug 30 '13 at 08:40

1

You need to add CHAR(13) between your two string.

SELECT @text nvarchar(MAX) = 'First Name : ' + @firstName + CHAR(13) + ' Second Name : ' + @secondName

answered Aug 30 '13 at 08:40

Sachin

39,043
7
86
102

New line in sql server

5 Answers5

Linked