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

sql-server character-encoding ssms

115,989

Solution 1

~~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.

Solution 2

You can paste the lines in from a text editor that uses UNIX-style line endings (CR+LF). I use Notepad++. First go to Settings/Preferences/New Document and change the format from Windows to Unix. Then open a new document, type in your lines, and copy them into SSMS.

Solution 3

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) + '%'

Solution 4

Either char(13) or char(10) would work. But it is recommended to use char(13) + char(10)

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

Solution 5

In SSMS, you can't print new line with select, just using PRINT instead

DECLARE @text NVARCHAR(100)
SET @text = concat(N'This is line 1.', CHAR(10), N'This is line 2.')
PRINT @text

View more solutions

115,989

MsBao

Updated on November 09, 2021

Comments

MsBao over 2 years

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?
- John Saunders about 15 years
  
  What will you be doing with this newline character once you get it? It's unusual to have formatting information in a database.
- dburges over 13 years
  
  Why are you typing data in through the data grid anyway? If this is lookup data that should be deployed to other servers, it shoudl be ina script that is under source control. Same with test data that might need to be recreated if the server is rebuilt. Real data generally comes in from the application or a formal, repeatable data import.
- MsBao over 13 years
  
  Because it's a feature of SSMS and I can. Who cares why?
- Solomon Rutzky almost 8 years
  
  Ronnie, the answer you have marked as "accepted" is quite specifically incorrect. I posted a working solution back in 2011 -- stackoverflow.com/a/4772453/577765 -- and it works in both VARCHAR and NVARCHAR columns. Is there a reason to keep the currently accepted answer as accepted?
- MsBao almost 8 years
  
  Copy/paste is not an answer to how you type something. I think the accepted answer is best.
- Solomon Rutzky almost 8 years
  
  @RonnieOverby Control-V is typing, is it not? Besides, the reason I even made my previous comment is because the wording of the question is: "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?". An UPDATE statement completely ignores the datagrid view, which is the context of the question. So I was just mentioning that even if it suited your needs, the accepted answer seems completely off-topic in terms of the question. It just seems confusing as it doesn't match up, that is all.
Yinda Yin about 15 years

AFAIK that's the only way to do it.
Yinda Yin about 15 years

However, that's certainly better than nothing, in a pinch. I would certainly loathe the prospect of writing SQL statements just to insert a line feed, as others have suggested. I know you can do it, it just grates.
topski about 15 years

But that's just a visual issue in SSMS. The CR/LF is really stored there.
MsBao about 15 years

That stores a music symbol: ♪
Yinda Yin about 15 years

Are you sure you're in SSMS? I'm using SSMS Express. Is there really a behavioral difference between the two programs?
Andomar about 15 years

I'm using SSMS 2008, maybe you have an older version?
Martin Smith over 13 years

@Ronnie - I get that as well. ALT + 0013 gives me a carriage return in notepad but doesn't work in SSMS
Solomon Rutzky over 13 years

-1 as this is actually an incorrect answer. It is possible to do this. Please see my answer below regarding copy/paste. Whether or not it is "preferable" to do this is another issue, but that was not the question.
John K over 12 years

This doesn't answer how to type a new line character in SMSS.
Pavel Chuchuva over 12 years

@JohnK Try using MS Access, as my answer suggests.
Yoshi almost 12 years

+1 because I got here from Google searching for "management studio newlines" and this solved my problem.
Robin Bennett over 10 years

Isn't CR+LF a windows line ending? Unix uses just LFhttp://en.wikipedia.org/wiki/Newline#Representations
Robin Bennett over 10 years

However, setting Notepad++ to Unix mode and pasting into SSMS worked for me. The line feeds aren't shown in grid mode, but they are if you return query results as text. (Turning on Show All Characters in Notepad++ is useful for checking).
Leland Barton about 10 years

But it is useful information within the context of the question.
Izzy over 8 years

This answer is incorrect. Relying on exact whitespace in your scripts is bad practice, the asker wanted alt codes which are much safer and obvious to a reader. -1 and I don't care that this is necro.
Jonathan Little over 8 years

One can also change the line ending format of the current document in Notepad++ by double clicking on the format listed in the application's bottom status bar. (It's to the right of the line/column/selection indicator.)
NASSER about 7 years

In SQL Server CHAR(10) + CHAR(13) = \n\r
ArcherBird over 5 years

cheesy but genius!