当前位置： 文江博客话题详情

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

发布于 2024-07-04 00:53:10 字数 67 浏览 8 评论 0原文

我没有看到任何关于这个主题的类似问题，我必须为我现在正在做的事情进行研究。 我想我会发布答案，以防其他人有同样的问题。

原文

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.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（11）

踏雪无痕 2024-07-11 00:53:10

char(13) 是 CR。 对于 DOS/Windows 风格的 CRLF 换行符，您需要 char(13)+char(10)，例如：

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

char(13) is CR. For DOS-/Windows-style CRLF linebreaks, you want char(13)+char(10), like:

'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'
回复 原文
愿得七秒忆 2024-07-11 00:53:10

我在这里找到了答案： 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

打印出以下内容：

这是第 1 行。
这是第 2 行。

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/

You just concatenate the string and insert a CHAR(13) where you want your line break.

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.

回复 原文
情独悲 2024-07-11 00:53:10

另一种方法是这样的：

INSERT CRLF SELECT 'fox 
jumped'

也就是说，只需在写入查询时在查询中插入换行符，就会将类似的换行符添加到数据库中。 这适用于 SQL Server Management studio 和查询分析器。 我相信如果您在字符串上使用 @ 符号，这也适用于 C#。

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

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.

string str = @"INSERT CRLF SELECT 'fox 
    jumped'"
回复 原文
狂之美人 2024-07-11 00:53:10

所有这些选项都取决于您的具体情况，但是如果您使用 SSMS，您可能看不到任何一个选项（正如一些评论中提到的 SSMS 隐藏 CR/LF）

所以不要自己开车弯曲，在

工具 | 选项

中检查此设置将取代

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)

So rather than driving yourself round the bend, Check this setting in

Tools | Options

which will replace the

回复 原文
驱逐舰岛风号 2024-07-11 00:53:10

在 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','`','
')

Run this in SSMS, it shows how line breaks in the SQL itself become part of string values that span lines :

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

How long is a blank line feed?
2

What are the ASCII values?
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) :

PRINT REPLACE('Line 1`Line 2`Line 3','`','
')
回复 原文
自演自醉 2024-07-11 00:53:10

我会说

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

或者

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

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.')
回复 原文
九厘米的零° 2024-07-11 00:53:10

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

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

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)

Good question, never done it myself :)

回复 原文
離人涙 2024-07-11 00:53:10

我来到这里是因为我担心我在 C# 字符串中指定的 cr-lfs 没有显示在 SQl Server Management Studio 查询响应中。

事实证明，它们在那里，但没有被显示。

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

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

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.

It turns out, they are there, but are not being displayed.

To "see" the cr-lfs, use the print statement like:

declare @tmp varchar(500)    
select @tmp = msgbody from emailssentlog where id=6769;
print @tmp
回复 原文
苏别ゝ 2024-07-11 00:53:10

下面是一个 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("");
    }
}

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 INSERT or UPDATE operations. It's got some of our proprietary error handling in it, but once you rip that out, it may be helpful -- I hope so.

/// <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("");
    }
}
回复 原文
笑咖 2024-07-11 00:53:10

在某些特殊情况下，您可能会发现这很有用（例如在 MS Report 中呈现单元格内容）

示例：

select * from 
(
values
    ('use STAGING'),
    ('go'),
    ('EXEC sp_MSforeachtable 
@command1=''select ''''?'''' as tablename,count(1) as anzahl from  ? having count(1) = 0''')
) as t([Copy_and_execute_this_statement])
go

此语句传递的不是一个包含 CR\LF 的字符串，而是一个包含一列（名为 [Copy_and_execute_this_statement] ）和三行的小表。 这可能适合消费者，他们吞下 CR\LF 但可以使用表（例如 MS Report ）另一个简单的例子是 select * from (values ('Adam'),('Eva')) as t([一些_名称]）

In some special cases you may find this useful (e.g. rendering cell-content in MS Report )

example:

select * from 
(
values
    ('use STAGING'),
    ('go'),
    ('EXEC sp_MSforeachtable 
@command1=''select ''''?'''' as tablename,count(1) as anzahl from  ? having count(1) = 0''')
) as t([Copy_and_execute_this_statement])
go

This statement delivers not a string with CR\LF in it, but a little table with one column ( named [Copy_and_execute_this_statement] ) and three rows. This is perhaps suitable for consumers, which swallow CR\LF but can consume tables ( e.g. MS Report ) another simple example would be select * from ( values ( 'Adam'),('Eva')) as t([some_name])

回复 原文
预谋 2024-07-11 00:53:10

这总是很酷，因为当您从 Oracle 等获取导出列表时，您会获得跨越多行的记录，这反过来对于 cvs 文件等可能很有趣，所以要小心。

无论如何，罗布的答案很好，但我建议使用@以外的东西，尝试更多，比如§§@@§§之类的，这样它就有机会获得一些独特性。 （但是，请记住要插入的 varchar/nvarchar 字段的长度..）

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.

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/nvarchar field you are inserting into..)

回复 原文
~没有更多了~

关于作者

撩动你心

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

15077827184

文章 0 评论 0

遗失的美好

文章 0 评论 0

离不开的别离

文章 0 评论 0

3857621955

文章 0 评论 0

懒猫

文章 0 评论 0

洋洋洒洒

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文