如何在 SQL Server VARCHAR/NVARCHAR 字符串中插入换行符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31057/
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 insert a line break in a SQL Server VARCHAR/NVARCHAR string
提问by Mark Struzinski
I didn't see any similar questions asked on this topic, and I had to research this for something I'm working on right now. Thought I would post the answer for it in case anyone else had the same question.
我没有看到关于这个主题的任何类似问题，我不得不为我现在正在做的事情研究这个。以为我会发布答案，以防其他人有同样的问题。
采纳答案by Mark Struzinski
I found the answer here: http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/
我在这里找到了答案：http: //blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-代码/
You just concatenate the string and insert a CHAR(13)where you want your line break.
您只需连接字符串并CHAR(13)在您想要换行的位置插入一个。
Example:
例子：
DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'
SELECT @text
This prints out the following:
这将打印出以下内容：
This is line 1.
This is line 2.
这是第 1 行。
这是第 2 行。
回答by S?ren Kuklau
char(13)is CR. For DOS-/Windows-style CRLFlinebreaks, you want char(13)+char(10), like:
char(13)是CR。对于 DOS-/Windows 样式的CRLF换行符，您需要char(13)+char(10)，例如：
'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'
回答by Frank V
Another way to do this is as such:
另一种方法是这样的：
INSERT CRLF SELECT 'fox
jumped'
That is, simply inserting a line break in your query while writing it will add the like break to the database. This works in SQL server Management studio and Query Analyzer. I believe this will also work in C# if you use the @ sign on strings.
也就是说，在编写查询时只需在查询中插入换行符，就会向数据库添加类似的中断。这适用于 SQL 服务器管理工作室和查询分析器。如果您在字符串上使用 @ 符号，我相信这也适用于 C#。
string str = @"INSERT CRLF SELECT 'fox
jumped'"
回答by AjV Jsy
Run this in SSMS, it shows how line breaks in the SQL itself become part of string values that span lines :
在 SSMS 中运行它，它显示了 SQL 本身的换行符如何成为跨行的字符串值的一部分：
PRINT 'Line 1
Line 2
Line 3'
PRINT ''
PRINT 'How long is a blank line feed?'
PRINT LEN('
')
PRINT ''
PRINT 'What are the ASCII values?'
PRINT ASCII(SUBSTRING('
',1,1))
PRINT ASCII(SUBSTRING('
',2,1))
Result :
Line 1
Line 2
Line 3
结果：
第 1
行第 2
行第 3 行
How long is a blank line feed?
2
一个空换行符有多长？
2
What are the ASCII values?
13
10
什么是 ASCII 值？
13
10
Or if you'd rather specify your string on one line (almost!) you could employ REPLACE()like this (optionally use CHAR(13)+CHAR(10)as the replacement) :
或者，如果您更愿意在一行中指定您的字符串（几乎！），您可以REPLACE()像这样使用（可选地CHAR(13)+CHAR(10)用作替换）：
PRINT REPLACE('Line 1`Line 2`Line 3','`','
')
回答by Rob Cooper
Following a Google...
跟随谷歌...
Taking the code from the website:
从网站上获取代码：
CREATE TABLE CRLF
(
col1 VARCHAR(1000)
)
INSERT CRLF SELECT 'The quick brown@'
INSERT CRLF SELECT 'fox @jumped'
INSERT CRLF SELECT '@over the '
INSERT CRLF SELECT 'log@'
SELECT col1 FROM CRLF
Returns:
col1
-----------------
The quick brown@
fox @jumped
@over the
log@
(4 row(s) affected)
UPDATE CRLF
SET col1 = REPLACE(col1, '@', CHAR(13))
Looks like it can be done by replacing a placeholder with CHAR(13)
看起来可以通过用CHAR(13)替换占位符来完成
Good question, never done it myself :)
好问题，我自己从来没有做过:)
回答by Bruce Allen
I got here because I was concerned that cr-lfs that I specified in C# strings were not being shown in SQl Server Management Studio query responses.
我来到这里是因为我担心我在 C# 字符串中指定的 cr-lfs 没有显示在 SQl Server Management Studio 查询响应中。
It turns out, they are there, but are not being displayed.
事实证明，它们在那里，但没有被显示。
To "see" the cr-lfs, use the print statement like:
要“查看”cr-lfs，请使用如下打印语句：
declare @tmp varchar(500)
select @tmp = msgbody from emailssentlog where id=6769;
print @tmp
回答by Ken Kin
I'd say
我会说
concat('This is line 1.', 0xd0a, 'This is line 2.')
or
或者
concat(N'This is line 1.', 0xd000a, N'This is line 2.')
回答by Carl Niedner
Here's a C# function that prepends a text line to an existing text blob, delimited by CRLFs, and returns a T-SQL expression suitable for INSERTor UPDATEoperations. It's got some of our proprietary error handling in it, but once you rip that out, it may be helpful -- I hope so.
这是一个 C# 函数，它在现有文本 blob 前面添加一个文本行，由 CRLF 分隔，并返回一个适用于INSERT或UPDATE操作的 T-SQL 表达式。它有一些我们专有的错误处理，但是一旦你把它撕掉，它可能会有所帮助——我希望如此。
/// <summary>
/// Generate a SQL string value expression suitable for INSERT/UPDATE operations that prepends
/// the specified line to an existing block of text, assumed to have \r\n delimiters, and
/// truncate at a maximum length.
/// </summary>
/// <param name="sNewLine">Single text line to be prepended to existing text</param>
/// <param name="sOrigLines">Current text value; assumed to be CRLF-delimited</param>
/// <param name="iMaxLen">Integer field length</param>
/// <returns>String: SQL string expression suitable for INSERT/UPDATE operations. Empty on error.</returns>
private string PrependCommentLine(string sNewLine, String sOrigLines, int iMaxLen)
{
String fn = MethodBase.GetCurrentMethod().Name;
try
{
String [] line_array = sOrigLines.Split("\r\n".ToCharArray());
List<string> orig_lines = new List<string>();
foreach(String orig_line in line_array)
{
if (!String.IsNullOrEmpty(orig_line))
{
orig_lines.Add(orig_line);
}
} // end foreach(original line)
String final_comments = "'" + sNewLine + "' + CHAR(13) + CHAR(10) ";
int cum_length = sNewLine.Length + 2;
foreach(String orig_line in orig_lines)
{
String curline = orig_line;
if (cum_length >= iMaxLen) break; // stop appending if we're already over
if ((cum_length+orig_line.Length+2)>=iMaxLen) // If this one will push us over, truncate and warn:
{
Util.HandleAppErr(this, fn, "Truncating comments: " + orig_line);
curline = orig_line.Substring(0, iMaxLen - (cum_length + 3));
}
final_comments += " + '" + curline + "' + CHAR(13) + CHAR(10) \r\n";
cum_length += orig_line.Length + 2;
} // end foreach(second pass on original lines)
return(final_comments);
} // end main try()
catch(Exception exc)
{
Util.HandleExc(this,fn,exc);
return("");
}
}
回答by Trubs
All of these options work depending on your situation, but you may not see any of them work if you're using SSMS(as mentioned in some comments SSMS hides CR/LFs)
所有这些选项都适用于您的情况，但如果您使用 SSMS，您可能看不到它们中的任何一个（如某些评论中提到的 SSMS 隐藏 CR/LF）
So rather than driving yourself round the bend, Check this setting in
因此，与其驾驶自己绕过弯道，不如检查此设置
Tools|Options
Tools|Options
回答by neslekkiM
This is always cool, because when you get exported lists from, say Oracle, then you get records spanning several lines, which in turn can be interesting for, say, cvs files, so beware.
这总是很酷，因为当你从 Oracle 导出列表时，你会得到跨越多行的记录，这对于 cvs 文件来说可能很有趣，所以要小心。
Anyhow, Rob's answer is good, but I would advise using something else than @, try a few more, like §§@@§§ or something, so it will have a chance for some uniqueness. (But still, remember the length of the varchar/nvarcharfield you are inserting into..)
无论如何，Rob 的回答很好，但我建议使用 @ 以外的其他东西，多尝试一些，比如 §§@@§§ 或其他东西，这样它就有机会获得一些独特性。（但是，请记住您要插入的varchar/nvarchar字段的长度..）
