如何将具有换行符的 SQL 值转换为 HTML 中的适当段落 <p>？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8874728/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert SQL value that has newlines into proper paragraphs <p> in HTML?
提问by TMC
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.
我有一个从 SQL 返回的文本，其中可能有换行符。但是，由于我将值放在<p></p>标签之间，因此忽略了换行符。
For example if the text in SQL is actually this (with a CRLF between lines):
例如，如果 SQL 中的文本实际上是这样的（行之间有 CRLF）：
foobar car
carbar foo
The HTML code becomes:
HTML 代码变为：
<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:
最终，我似乎想检测换行符，然后添加适当的 HTML，例如：
<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?
这应该在前端处理还是在返回数据的 SQL 查询中处理？似乎应该在前端代码（在我的 ASP.NET 视图中）上处理它，但如果是这样，如何处理？
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 :)
我注意到 PHP 中有一个函数可以处理这种情况：nl2br（http://www.php.net/manual/en/function.nl2br.php）但不幸的是这对我没有帮助:)
回答by Code Magician
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:
我推荐的方法是编写一些 .net 代码来用<br/>标签替换 cr 和 lf 。代码本身很简单，下面是一个模拟 php nl2br 函数的示例：
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.
您也可以使用REPLACE()和CHAR()在 SQL 中执行这种转换，尽管最好在表示层而不是数据库层中执行这种转换。
The SQL approach would be:
SQL 方法是：
SELECT REPLACE(REPLACE(myStr, char(13), '<br/>'), CHAR(10), '<br/>')
This would replace CHAR(13)and CHAR(10)(cr and lf) with <br/>tags.
这将用标签替换CHAR(13)和CHAR(10)（cr 和 lf）<br/>。
A third option would be to render your text at preformatted textusing 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)
第三种选择是使用标签以预先格式化的文本呈现文本，<pre>尽管这实际上只适用于一小部分用例（例如在 StackOverflow 上显示此处使用的代码）
e.g.
例如
<pre>
foobar car
carbar foo
</pre>
which would render like this:
它将呈现如下：
foobar car carbar foo
keeping spaces and new lines intact.
保持空格和新行完整。
回答by Gustavo F
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:
您可以在 sql server 或您的应用程序中执行此操作，要在 sql server 中执行此操作，您可以使用以下代码替换回车符：
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:
您可以通过 SqlDataReader 以更优雅的形式处理 .Net 代码，并更改文本：
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 />");
回答by SteveK
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:
你真的应该用你的网络语言（我猜是 asp.net）来做这件事，但如果你必须用 SQL 来做，这是它的完成方式：
MySQL:
MySQL：
SELECT REPLACE(column_name, "\r\n", "<br/>");
T-SQL (MS SQL Server):
T-SQL（MS SQL Server）：
SELECT REPLACE(REPLACE(column_name, CHAR(13), ''), CHAR(10), '<br/>');
回答by kba
Just process your data in ASP.NET. There is no reason to put that workload on the SQL server.
只需在 ASP.NET 中处理您的数据。没有理由将该工作负载放在 SQL 服务器上。
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>
回答by SQLMenace
If you want to do this in SQL Server then one way is to use the replacefunction to replace char(13) + char(10) with
如果您想在 SQL Server 中执行此操作，那么一种方法是使用该replace函数将 char(13) + char(10) 替换为
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
