How to convert SQL value that has newlines into proper paragraphs <p> in HTML?

Question

I have a text being returned from SQL that may have newlines in it. However, since I'm putting the value in between <p></p> tags, it's ignoring the newlines.

For example if the text in SQL is actually this (with a CRLF between lines):

foobar car
carbar foo

The HTML code becomes:

<p>foobar car
carbar foo</p>

Which is just rendering as:

foobar car carbar foo

Ultimately it seems that I want to detect a newline then add the appropriate HTML such as:

<p>foobar car</p><p>carbar foo</p>

or even

<p>foobar car<br/><br/>carbar foo</p>

Should this be handled on the front end or in the SQL queries returning the data? It seems that it should be handled on the front end code (in my ASP.NET view) but if so, how?

I noticed that there a function in PHP to handle exactly this case: nl2br (http://www.php.net/manual/en/function.nl2br.php) but unfortunately that doesn't help me :)

Answer 1

It's generally better to perform this kind of logic is your presentation code then your DB or middle-tier.

My Recommended approach would be to write some .net code to replace cr and lf with <br/> tags. The code itself is straightforward, here is an example that will emulate your php nl2br function:

string nl2br(string text)
{
    return text.Replace("\r", "<br/>").Replace("\n", "<br/>");
}

You could also perform this transform in SQL using REPLACE() and CHAR() although it is preferable to perform this kind of transform in the presentation layer, not the database layer.

The SQL approach would be:

SELECT REPLACE(REPLACE(myStr, char(13), '<br/>'), CHAR(10), '<br/>')

This would replace CHAR(13) and CHAR(10) (cr and lf) with <br/> tags.

A third option would be to render your text at preformatted text using the <pre> tags although this is really only suitable for a narrow set of use-cases (such as displaying code as used here on StackOverflow)

e.g.

<pre>
    foobar car 
    carbar foo
</pre>

which would render like this:

foobar car 
carbar foo

keeping spaces and new lines intact.

Answer 2

you can do this in sql server or in your application, to do this in sql server you can replace the carriage return char with this code:

select REPLACE(Comments,CHAR(13),' <br />') from Production.ProductReview

EDIT:

You can do this in a more elegant form handling in .Net code, through SqlDataReader, and changing the text:

        var target = "this is the line \r\n and this is another line";

        Regex regex = new Regex(@"(\r\n|\r|\n)+");

        string newText = regex.Replace(target, "<br />");

Answer 3

You should really be doing this in your web language (asp.net I guess), but if you must do it in SQL, here's how it's done:

MySQL:

SELECT REPLACE(column_name, "\r\n", "<br/>");

T-SQL (MS SQL Server):

SELECT REPLACE(REPLACE(column_name, CHAR(13), ''), CHAR(10), '<br/>');