SQL：在 varchar 字符串中插入换行符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36608252/
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: Insert a linebreak in varchar string
提问by 90abyss
I've searched StackOverflow for all the possible solutions concerning how to insert a linebreak in a SQL text string. I've referred this link but to no avail. How to insert a line break in a SQL Server VARCHAR/NVARCHAR string
我已经在 StackOverflow 中搜索了有关如何在 SQL 文本字符串中插入换行符的所有可能解决方案。我已经提到了这个链接，但无济于事。如何在 SQL Server VARCHAR/NVARCHAR 字符串中插入换行符
But none of the solutions are working for me.
但是没有一个解决方案对我有用。
This is what I'm trying to do:
这就是我想要做的：
insert into sample (dex, col)
values (2, 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.')
But this is the output generated: (Select Col from sample where dex = 2)
但这是生成的输出：（从样本中选择 Col，其中 dex = 2）
This is line 1. This is line 2.
这是第 1 行。这是第 2 行。
This is the output that I desire:
这是我想要的输出：
This is line 1.
This is line 2.
这是第 1 行。
这是第 2 行。
I'm using SQL server and SSMS if that helps.
如果有帮助，我正在使用 SQL 服务器和 SSMS。
Any ideas why it isn't working?
任何想法为什么它不起作用？
采纳答案by DhruvJoshi
Well your query works perfectly fine. SSMS by default shows all query out put in the grid view, which does not display the line break character.
那么你的查询工作得很好。默认情况下，SSMS 显示网格视图中的所有查询输出，不显示换行符。
To see it you can switch to text view using cntrl+ Tshortcut or like below
要查看它，您可以使用cntrl+T快捷方式或如下所示切换到文本视图
The results I got for your query are below( and they work)
我为您的查询得到的结果如下（它们有效）
回答by Lukasz Szozda
It works perfectly:
它完美地工作：
CREATE TABLE sample(dex INT, col VARCHAR(100));
INSERT INTO sample(dex, col)
VALUES (2, 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.');
SELECT *
FROM sample;
Output:
输出：
The "problem" is SSMSgrid view that skips newline characters (and others too). Otherwise you will get different rows height like in Excel.
“问题”是SSMS跳过换行符（以及其他字符）的网格视图。否则，您将获得不同的行高，如Excel.
You could observe the same behaviour in SEDE.
您可以在SEDE.
LiveDemo-SEDELiveDemo-SEDE-TextView
LiveDemo-SEDELiveDemo-SEDE-TextView
Output:
输出：
You could compare it using:
您可以使用以下方法进行比较：
SELECT 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.';
PRINT 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.';
回答by Thomas G
The CR/LF chars are there, it's just that in the format of your output, they are being ignored.
CR/LF 字符就在那里，只是在您的输出格式中，它们被忽略了。
I've created a fiddle to illustrate this, with 2 VARCHARcolumns. In the first one I insert the text with no CR/LF, in the second I include them
我创建了一个小提琴来说明这一点，有 2VARCHAR列。在第一个我插入文本 no CR/LF，在第二个我包括它们
CREATE TABLE sample (dex INT, colnocr VARCHAR(50), col VARCHAR(50)) ;
insert into sample (dex, colnocr, col) values
(2,
'This is line 1.' + 'This is line 2.',
'This is line 1.' + CHAR(13) + CHAR(10) + 'This is line 2.'
)
;
if you run the query
如果您运行查询
SELECT * FROM sample
The result in plain textare:
纯文本的结果是：
| dex | colnocr | col |
|-----|--------------------------------|----------------------------------|
| 2 | This is line 1.This is line 2. | This is line 1.
This is line 2. |
but if you run it in tabular:
但如果你在表格中运行它：
dex colnocr col
2 This is line 1.This is line 2. This is line 1. This is line 2.
Check it : SqlFiddleDemo
核实 ： SqlFiddleDemo
回答by user2092071
A bit late to this discussion, but in SSMS 2016, there is an option on the Tools | Options menu under Query Results / SQL Server / Results to Grid called "Retain CR/LF on copy or save". Checking this box will allow you to copy values from a cell in a grid result to, say, another query window and still have the line breaks.
这个讨论有点晚了，但在 SSMS 2016 中，工具 | 中有一个选项。Query Results / SQL Server / Results to Grid 下的选项菜单称为“在复制或保存时保留 CR/LF”。选中此框将允许您将值从网格结果中的单元格复制到另一个查询窗口，并且仍然有换行符。
