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

User · Answer

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

User · Answer

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

User · Answer

This is possible if you have an existing Newline character in the row or another row   Select the square-box that represents the existing Newline character  copy it  control-C   and then paste it  control-V  where you want it to be   This is slightly cheesy  but I actually did get it to work in SSMS 2008 and I was not able to get any of the other suggestions  control-enter  alt-13  or any alt-    to work

User · Answer

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

User · Answer

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

User · Answer

I tried entering a new line character using an excel Sheet  I entered my data by writing  abc  below a blank cell in Excel and then updated my db using the empty cell above the cell containing  abc  and the cell with data  I copy pasted both the cells in my SSMS  It worked for me

User · Answer

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

User · Answer

I use INSERT  a    Char 10     b  INTO wherever WHERE whatever

User · Answer

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

User · Answer

I had trouble initially  don t know why  but I finally got the following to work with SSMS for SQL Server 2008   Insert ALT-13 then ALT-10 where desired in your text in a varchar type column  music symbol and square appear and save when you leave the row    Initially you ll get a warning    to the left of the row after leaving it   Just re-excecute your SELECT statement   The symbols and warning will disappear but the CR LF is saved   You must include ALT-13 if you want the text displayed properly in HTML   To quickly determine if this worked  copy the saved text from SSMS to Notepad   Alternatively  if you can t get this to work  you can do the same thing starting with an nvarchar column   However the symbols will be saved as text so you must convert the column to varchar when you re done to convert the symbols to CR LFs   If you want to copy  amp  paste text from another source  another row or table  HTML  Notepad  etc   and not have your text truncated at the first CR  I found that the solution  Programmer s Notepad  referred to at the following link works with SSMS for SQL Server 2008 using varchar  amp  nvarchar column types   http   dbaspot com sqlserver-programming 409451-how-i-enter-linefeed-when-modifying-text-field-2 html post1523145  The author of the post  dbaspot  mentions something about creating SQL queries - not sure what he means   Just follow the intructions about Programmer s Notepad and you can copy  amp  paste text to and from SSMS and retain the LFs both ways  using Programmer s Notepad  not Notepad    To have the text display properly in HTML you must add CRs to the text copied to SSMS   The best way to do this is by executing an UPDATE statement using the REPLACE function to add CRs as follows   UPDATE table name SET column name   REPLACE column name   CHAR 10   CHAR 13    CHAR 10

User · Answer

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    lt ctrl gt enter    lt alt gt  enter    lt ctrl gt  lt alt gt  enter    lt shift gt  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   lt br gt     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

User · Answer

I find the easy way to do it for non-repeatable updates is to use MS Access and create a linked table  then update the data as you need   I guess the MS Access team doesn t talk to the SMSS team

User · Answer

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

User · Answer

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

[sql-server] How to type a new line character in SQL Server Management Studio

Examples related to sql-server

Examples related to character-encoding

Examples related to ssms