CODEWITHSUNDEEP
asp.netsqlsql-server
TMC
TMC

Reputation: 8154

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

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 :)

Upvotes: 6

Views: 27388

Answers (5)

Code Magician
Code Magician

Reputation: 24032

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.

Upvotes: 14

kba
kba

Reputation: 19476

Just process your data in ASP.NET. There is no reason to put that workload on the SQL server.

text = text.Replace("\r", "</p>\n<p>").Replace("\n","</p>\n<p>");
text = "<p>" + text + "</p>";

The above code will turn

foobar car
carbar foo

Into

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

Upvotes: 0

SteveK
SteveK

Reputation: 1006

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/>');

Upvotes: 1

Gustavo F
Gustavo F

Reputation: 2206

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 />");

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 135181

If you want to do this in SQL Server then one way is to use the replace function to replace char(13) + char(10) with

example

declare @s varchar(1000)

select @s = 'line1
line2'

select replace(@s,char(13) + char(10),'<br><br>')

output

line1<br><br>line2

so in your case

select replace(ColumnName,char(13) + char(10),'<br><br>')
From SomeTable

Upvotes: 0

Related Questions