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

Question

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?

Answer 1

this works for me (View results as text -- results as grid hides the newlines)

select '

a 

b

'

Answer 2

I use INSERT 'a' + Char(10) + 'b' INTO wherever WHERE whatever

Answer 3

Ronnie,

The data grid appears to be swallowing any attempts to paste a newline character, including the use of ALT+010. Microsoft does not list any shortcut keys that would help. The usual suspects

<ctrl>enter, 
<alt> enter, 
<ctrl><alt> enter, 
<shift> enter, etc

don't work, as you pointed out.

For the record, if you are doing web development, the linefeeds will not show up in the browser anyway (they are interpreted as extra whitespace, and are ignored). I had to replace line feed with

<br>

everywhere I wanted a line break to show up in the browser (text areas will accept line feeds as usable input).

Even if you could insert a line break, I don't think the data grid view in SSMS is capable of displaying it.

Answer 4

You can't.

Use a "new query" window instead, and do a manual update:

UPDATE mytable
SET textvalue = 
'This text
can include
line breaks'
WHERE rowid = 1234

Answer 5

You're talking about right-clicking on a table and selecting "Edit Top 50 Rows", right?

I tried [Ctl][Enter] and [Alt][Enter], but neither of those works.

Even when I insert data with CR/LF (using a standard INSERT statement), it shows up here in a single line with a rectangle representing the control codes.

Answer 6

You can prepare the text in notepad, and paste it into SSMS. SSMS will not display the newlines, but they are there, as you can verify with a select:

select *
from YourTable
where Col1 like '%' + char(10) + '%'

Answer 7

If you are trying to enter data directly into the table in grid view (presumably Right Click TableName and Select Open Table), then you can enter your unicode text string and wherever you want a carriage return just type 13 with the alt key pressed in the numeric keypad.

That would be Alt+13. This works only from the numeric keypad and does not work with the number keys on the top of the keyboard. The carriage return will be stored as a square

Answer 8

Try using MS Access instead. Create a new file and select 'Project using existing data' template. This will create .adp file.

Then simply open your table and press Ctrl+Enter for new line.

Pasting from clipboard also works correctly.

Answer 9

either char(13) or char(10) would work. but it is recommended to use char(13) + char(10)

• char(13) = \n - new line
• char(10) = \r - go to the beginning of the line