Search code examples
sql-servercharacter-encodingssms

How to type a new line character in SQL Server Management Studio

In the "datagrid" view of an open table of data, how can I type a new line character into an nvarchar field directly in SSMS?

Is there an alt code?

Solution

  • You can't. (Edit: other good answers have been posted with some workable methods.)

    My preferred method to do this is via a direct SQL update.

    Two general techniques:

    --Literal returns inside strings
UPDATE mytable
SET textvalue = 
'This text
can include
line breaks'
WHERE rowid = 1234

--Concatenating CHAR codes
UPDATE mytable
SET textvalue = 'This text' + CHAR(10) + CHAR(13) 
   + 'can include' + CHAR(10) + CHAR(13)
   + 'line breaks'
WHERE rowid = 1234

    The former is a little easier to work with, but could give inconsistent results if you paste in text from outside sources with unknown line-ending codes.

    The latter is somewhat harder to work with but is more likely to give you consistent and reliable results.