0

I'd like to insert a text with line break into a SQL Server 2016 TEXT COLUMN (NOT VARCHAR/NVARCHAR!). My different tries didn't bring any effort like:

UPDATE table SET col = 'My
text';

-- Result:  'My  text'

or

UPDATE table SET col = 'My<br>text'; 

-- Result:  'My<br>text'

or

UPDATE table SET col = 'My' + CHAR(13) +'text'; 

-- Result:  'My text'

How does this work?

vso
  • 199
  • 2
  • 17
  • 3
    Please don't use the datatype text as it will be removed in future SQL server versions, it is advised by Microsoft to switch to `nvarchar(MAX)` https://learn.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql – WhatsThePoint Mar 05 '18 at 08:47
  • 3
    If you're using this on windows, then you'd have to add CHAR(10) after CHAR(13) as well. – ayushgp Mar 05 '18 at 08:47
  • 1
    How exactly are you reviewing the results? – Radu Gheorghiu Mar 05 '18 at 08:48
  • Executing UPDATE command in a sql query window and afterwards a SELECT statement in the same window (in the next line). Then copy the value from results window and insert into the sql query window (or notepad++). I've got another column with line breaks inside. Copying that value and inserting it into the same sql query window shows it with line breaks. – vso Mar 05 '18 at 08:54
  • 2
    Show results as text, not as grid. Then you can see the line breaks. – Zohar Peled Mar 05 '18 at 08:55
  • 2
    Possible duplicate of [How to insert a line break in a SQL Server VARCHAR/NVARCHAR string](https://stackoverflow.com/questions/31057/how-to-insert-a-line-break-in-a-sql-server-varchar-nvarchar-string) – WhatsThePoint Mar 05 '18 at 08:55
  • OK, showing the results as text DOES show the line breaks after inserting them with CHAR(13) + CHAR(10). Don't know why in the other case copying the values from the grid results also shows the line breaks but not in this case. Thanks for your help! – vso Mar 05 '18 at 09:13

0 Answers0