如何在SQL Server VARCHAR/NVARCHAR字符串中插入换行？

Question

如何在SQL Server VARCHAR/NVARCHAR字符串中插入换行？

687

我没有看到有类似的问题讨论这个话题，并且我必须为我正在做的某些事情进行研究。想着我会把答案发出来，以便如果其他人有同样的问题的话，可以参考。

- Mark Struzinski

10

测试您的输出时，如果使用SSMS，请确保选中“保留复制或保存的CR / LF”选项，否则所有粘贴的结果将丢失换行符。您可以在设置中找到此选项，具体路径为：查询结果 -> SQL Server -> 到网格结果。 - Stefanos Zilellis

1

@StefanosZilellis，请确保打开一个新的查询窗口以使设置更改生效。 - Don Cheadle

11个回答

341

我在这里找到了答案：http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/

您只需将字符串连接起来，在需要换行的位置插入 CHAR(13)。

示例：

DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'
SELECT @text

这将打印以下内容：

这是第一行。
这是第二行。

- Mark Struzinski

21

似乎你需要使用 PRINT @text 而不是 SELECT 来获得这个结果。 - QMaster

3

顺便说一下：您还可以使用NCHAR(0x1234)来获取Unicode字符。这对于插入换行符并非必需，但如果需要插入或搜索Unicode字符，则可能会派上用场。 - Paul Groke

4

在 SQL Server 2016 中，只有使用 print 而不是 select 才会打印出这两行，例如：

DECLARE @text NVARCHAR(100);
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.';
print @text;

- devinbost

16

为了测试你的输出结果，如果你使用SSMS，请确保选中“复制或保存时保留CR/LF”选项，否则所有粘贴的结果将失去换行符。你可以在设置 -> 查询结果 -> SQL Server -> 结果到网格中找到此选项。 - Don Cheadle

4

@DonCheadle说的是，你可能需要关闭查询标签页并重新打开它，以使该设置生效。;) - James Wilkins

显示剩余2条评论

105

另一种做法是这样的：

INSERT CRLF SELECT 'fox 
jumped'

也就是说，在编写查询时插入一个换行符将会在数据库中添加相应的换行符。这在SQL Server Management Studio和Query Analyzer中都有效。如果您在C#中使用@符号的字符串，我相信这种方法也可以实现。

string str = @"INSERT CRLF SELECT 'fox 
    jumped'"

- Frank

17

换句话说，SQL语言的语法仅允许在字符串字面量中使用原始换行符。我已经尝试过所有引擎（包括SQL Server、Oracle、MySQL、PostgreSQL和SQLite），都是这样工作的。 - Álvaro González

有时候，如果你在存储过程中使用它，它会随机停止工作。 - DaFi4

53

所有这些选项都适用于您的情况，但是如果您使用的是SSMS(如一些评论中所述)，则可能不会看到它们之一起作用，因为SSMS隐藏CR/LFs。

因此，与其让自己困扰不已，不如在

工具 | 选项中检查此设置

- Trubs

7

我使用的是SSMS v18.2版本，并且在尝试更改设置时遇到了麻烦。我必须选中它，然后退出SSMS并重新启动。请确保只有1个SSMS实例正在运行，第二个实例会用原始值覆盖该设置。但最终我成功了。+1 - Jay Cummins

3

请注意，如果您将输出设置为文本而不是网格，则回车换行将按预期保留，无需对应用程序选项进行任何更改。 - Code Ranger

1

需要重新启动SSMS才能使此更改生效。 - PAS

36

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

结果：
行1
行2
行3

一个空白换行符有多长？
2

ASCII 值是什么？
13
10

或者，如果你更愿意在一行上指定你的字符串（几乎！），你可以这样使用 REPLACE()（可选择使用 CHAR(13)+CHAR(10) 作为替换）：

PRINT REPLACE('Line 1`Line 2`Line 3','`','
')

- AjV Jsy

18

在Google上查找后，可以得到以下网站代码：

从网站中获取的代码：

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

看起来可以通过将占位符替换为CHAR(13)来完成

好问题，我自己从来没有做过 :)

- Rob Cooper

4

但如果文本中有电子邮件地址呢？"jon@bob.com" 将变成 "jon\nbob.com"（e-mail 地址换行）。 - intrepidis

4

@ChrisNash 如果想要使用不同的占位符（例如“|”，“~”或多个字符，“!#!”），请参考下面的答案：https://dev59.com/YnVD5IYBdhLWcg3wR5go#31179。 - bradlis7

1

"CONCAT (CHAR(13) , CHAR(10))"（"\r\n"）在Windows环境下更好，我假设这是情况（SQL Server）。http://www.cs.toronto.edu/~krueger/csc209h/tut/line-endings.html - d.popov

这是非确定性的。表被定义为一组无序记录。不能保证数据库引擎会按照插入顺序返回记录。如果没有第二列以某种方式保存顺序（一个简单的IDENTITY()就可以），并且在输出查询中使用了ORDER BY，服务器将以它发现方便的任何顺序返回记录。那可能是它们插入的相同顺序，但是服务器允许按照自己的意愿进行操作而不需要ORDER BY。 - Bacon Bits

13

我来到这里，是因为我担心在 C# 字符串中指定的回车换行符（cr-lfs）没有显示在 SQL Server Management Studio 查询响应中。

事实证明，它们确实存在，但并未被显示出来。

要“查看” cr-lfs，请使用打印语句，如：

declare @tmp varchar(500)    
select @tmp = msgbody from emailssentlog where id=6769;
print @tmp

- Bruce Allen

11

I'd say

concat('This is line 1.', 0xd0a, 'This is line 2.')

或者

concat(N'This is line 1.', 0xd000a, N'This is line 2.')

- Ken Kin

2

还要检查@Trubs答案中的SSMS配置：https://dev59.com/YnVD5IYBdhLWcg3wR5go#59189881 - Jay Cummins

或者 + CAST(0xD0A AS VARCHAR(1)) + - Ivan Silkin

@IvanSilkin 在你的语句中，我认为是 varchar(2)。否则你只会说 0xd。 - undefined

5

这里是一个C#函数，它在现有的文本块前添加一行文本，由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("");
    }
}

- Carl Niedner

3

这总是很酷的，因为当你从Oracle等地方获得导出列表时，你会得到跨越几行的记录，这反过来可能对cvs文件有所影响，所以要小心。

无论如何，Rob的答案很好，但我建议使用比@更多的其他符号，比如§§@@§§之类的，这样它就有机会具有一些独特性。(但仍然要记住你正在插入的varchar / nvarchar字段的长度..)

- neslekkiM

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Soeren Kuklau · Accepted Answer

727

char(13) 是代表回车符（CR）。如果想要使用 DOS/Windows 风格的 CRLF 换行符，需要使用 char(13)+char(10) 来表示：

'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'

- Soeren Kuklau

38

在 Windows 中，char(13)+char(10) 对我没用。我只使用了 char(10)。 - nima

8

@Nima：一些应用程序会使用其中一个或两个来显示新行，但是许多应用程序可能需要两个连续出现来表示新行。我认为最好同时使用两个。您可以在此列出无法使用此方法的应用程序。我个人更喜欢使用十六进制值CHAR(0x0D) + CHAR(0x0A)，但每个人有自己的偏好。 - MikeTeeVee

2

我成功地使用了这种方法，但是遇到了一个问题：一旦您有超过约480个+，SQL Server就会抱怨您的查询嵌套太深。我的解决方案是改用Rob Cooper的答案，但使用更长和更难懂的令牌。 - Marcus Downing

8

提供\r\n将意味着承认正则表达式的存在，并且有用户能够理解和使用它们。 - wwmbes

10

在Java出现之前，\r和\n就已经在C语言中使用了几十年，并且它们是Python、PHP、Ruby、C++、C#等语言的标准。 - Uueerdo

显示剩余2条评论