How do I store text in database with line breaks or paragraph

Question

In my application I have Terms for Apps. Before I used to put this in front-end with HTML .

But now I need to put this text in database in a column called AppTerms inside table called App.

I referred to this link in stackoverflow and followed like this :

UPDATE [AppsDatabase].[dbo].[App]
SET AppTerms = 'This App has no minimum term.' + CHAR(13) +
'This App is built and is supported through the standard way of using the company online support.' + CHAR(13) +
'Incorrectly formatted data will not be formatted but may be charged for.'
 WHERE AppID = 8
 GO

But I am not getting line breaks here. And also can someone tell me how can i put bullet points in each line?

Can you paste the output of this command? `DECLARE @Terms NVARCHAR(MAX) = (SELECT AppTerms FROM AppsDatabase.dbo.App WHERE AppID = 8;); PRINT @Terms;` — Iain Samuel McLean Elder, Dec 03 '13 at 13:45
@IainElder When I used your command, I can see text formatted with line breaks in output window. So I think I am doing mistake in front end. When I am calling this, text is not formatting. — Ajay, Dec 03 '13 at 13:57

KMB · Accepted Answer · 2013-12-03T13:45:48.767

5

If you are showing HTML you need to store  instead of CHAR(13).

Displaying this text in HTML will yield the correct display. In addition you can wrap a paragraph in ....

For bullet lists look at http://www2.gol.com/users/billp/articlehtml/bullet.html.

This is under the assumption that you take the text out and display in HTML.

edited Dec 03 '13 at 13:45

answered Dec 03 '13 at 13:44

KMB

162
3

but if i use , it shows incorrect syntax error. I tried to use like this : + + – Ajay Dec 03 '13 at 14:53
The needs to be part of the text - HTML will then convert it into a line break. It is not possible to show the text in SQL with line breaks - this would require adding the CHAR(13) which is ignored by HTML. – KMB Dec 06 '13 at 10:08

score 2 · Answer 2 · answered Jun 28 '14 at 00:02

2

It might be too late, yet, I had the same problem... The solution I came up with is to store paragraphs(or lists... or whatever) in a separate child table and handle the formatting at the server programming language level.

answered Jun 28 '14 at 00:02

user3134422

59
8

SAS · Answer 3 · 2013-12-03T13:56:23.417

0

It should work. In HTML you could use the  tag.

edited Dec 03 '13 at 13:56

answered Dec 03 '13 at 13:44

SAS

3,943
2
27
48

I had trouble with displaying the db-tag but now the suggested answer should be visible. – SAS Dec 03 '13 at 13:48
I was referring to "It should work. What datatype is your column?" That doesn't belong in an answer. – rory.ap Dec 03 '13 at 13:50

Iain Samuel McLean Elder · Answer 4 · 2013-12-03T17:43:32.903

You want the application to render HTML with line breaks, but you're storing plain text.

You need to store HTML instead. Encode the line breaks using the   element.

Try this:

UPDATE [AppsDatabase].[dbo].[App]
SET AppTerms = 'This App has no minimum term.<br>' + CHAR(13) +
'This App is built and is supported through the standard way of using the company online support.<br>' + CHAR(13) +
'Incorrectly formatted data will not be formatted but may be charged for.'
 WHERE AppID = 8;

If you want to encode bullet points, check out the <ul> and <li> elements.

This is a data formatting issue more than a SQL Server issue. You would have had the same problem if you tried to store the data in a file instead of a table.

The W3C specification for HTML Text explains how white space is handled in HTML:

In HTML, only the following characters are defined as white space characters:

ASCII space ( )

ASCII tab (	)

ASCII form feed ()

Zero-width space ()

Line breaks are also white space characters.

[...] user agents should collapse input white space sequences when producing output inter-word space.

Line breaks are equivalent to spaces, and multiple white space characters are collapsed to a single space in the output.

How do I store text in database with line breaks or paragraph

4 Answers4