将换行符转换为 html 的 sql 查询 <br>
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7177007/
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
sql query to convert new line character to a html <br>
提问by mjr
I have a mysql database of articles that were entered into a textarea with no formatting, they use only a newline character for line breaks
我有一个文章的 mysql 数据库，这些文章被输入到没有格式的文本区域中，它们只使用换行符作为换行符
\n
I need to convert all of these into html br tags
我需要将所有这些转换为 html br 标签
<br />
can you help me write a query to do this that I can run in phpmyadmin that will do this?
你能帮我写一个查询来做到这一点，我可以在 phpmyadmin 中运行它吗？
the name of the table is
表的名称是
exp_channel_data
as a bonus question...
作为奖励问题...
Is there a query I can run that will strip everything out of the middle of p and span tags
是否有我可以运行的查询，它将从 p 和 span 标签的中间剥离所有内容
I want to get rid of this stuff
我想摆脱这些东西
<p class="MsoNormal" style="MARGIN: 0in 0in 0pt">
<span face="Times New Roman">
and end up with just this
并最终得到这个
<p>
<span>
回答by Crack
First question:
第一个问题：
UPDATE exp_channel_data SET text_column = REPLACE(text_column, '\r\n', '<br />')
If it doesn't replace anything, use '\n'instead of '\r\n'.
如果它不能代替任何东西，请使用'\n'代替'\r\n'。
Second question:
第二个问题：
You can't do it with a SQL query, you have to fetch this data into a PHP script, or anything else you like, and perform a regular expression replace (example for PHP):
你不能用 SQL 查询来做到这一点，你必须将这些数据提取到 PHP 脚本或其他任何你喜欢的东西中，然后执行正则表达式替换（例如 PHP）：
$new_str = preg_replace('#<(p|span)[^>]+>#', '<>', $old_string);
回答by Jacob
UPDATE yourTable SET text=REPLACE(text,"\n","<br />")
This works for your first question.
这适用于您的第一个问题。
回答by Derek Hogue
Since you're using ExpressionEngine, you can just change the format of each field to XHTML. It will add the <br />markup when displayed on the front-end. bet to keep your data clean and leave the formatting to the parser displaying it.
由于您使用的是 ExpressionEngine，您只需将每个字段的格式更改为 XHTML。它会<br />在前端显示时添加标记。打赌保持您的数据干净，并将格式留给显示它的解析器。
