主页 / dba / 问题 / 300198
Accepted
John K. N.
Asked: 2021-09-27 23:21:26 +0800 CST

ntext 列中 REPLACE() 的替代解决方案

  • 772

概述

我有一个图片数据库，其中包含用相机拍摄的图片的目录信息。该数据库包含一个表，该表pictures有 256 列，其中包含有关已拍摄照片的信息。

一列Comments被格式化为ntext并包含 CR/LF。
还有另外 21 列已配置为ntext.

Tasks | Export Data...我使用SSMS 中的函数将数据库中的数据提取到一个平面文件中。导出的数据随后由外部合作伙伴传输到将在不久的将来使用的新系统。导出文件 (CSV) 包含大约 256 列，其中 21 列可能包含 CR/LF。

问题

Comments字段/列包含许多影响数据分析的CR /LF (SQL: CHAR(13), )。CHAR(10)

我尝试使用REPLACE(expression, value, value)来搜索 CR/LF 并将其替换为并正在考虑在使用SSMS@@的导出期间实现此功能。Export Data

但是，该REPLACE()函数返回一个

消息 8116，级别 16，状态 1，第 4 行
参数数据类型 ntext 对于替换函数的参数 1 无效。

...当我执行以下操作时：

SELECT 'Start *******************', REPLACE(Comment,'
','@@'), ID, '********************End' FROM dbo.pictures
WHERE Comment LIKE '%
%';

来自列注释的示例数据

发表以下声明：

SELECT Comment FROM dbo.Pictures
WHERE Comment like '%
%';

...将检索以下示例Comment记录：

Zwei Fotos von Arenenberg auf einer Seite einer englischen Zeitschrift.
Seite 148 der Zeitung "The Graphic" vom 4. August 1906 = News from Abroad.
"The last stage of all": the retreat for aged actors opened last week near Meaux, in France
1. General view of the home
2. M. Coquelin reciting in the open-air theater

The château of Arenenberg which has been presented by the Empress Eugénie to the canton of Thurgovie
3. View from the chateau [Arenenberg] over Lake Constance
4. The château of Arenenberg
The Empress Eugénie has presented to the Swiss Canton of Thurgovie the historical château of Arenenberg, where Napoleon III. passed several years of his youth. Queen Hortense, on the fall of the first Empire, fled to Switzerland, and in 1817 purchased the castle, which is delightfully situated on the shore of Lake / Constance. The gift includes a priceless collection of paintings, manuscripts, books, old furniture, and tapestries, among the mos important souvenirs being the camp bed of Napoleon III., and the carriage in which he left Sedan after his defeat. When the alterations are complete the château will be opened to the public.

5. The maiden voyage of the new Santos-Dumont flying machine
6. The room in Viborg where the dissolved Duma met
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          .

是的，有一个空行。我自己插入了句号以显示文本的长度。

常用表达

我导出数据，然后运行各种正则表达式以从数据中省略 CR/LF。因为该列Comment位于数据中间，所以我不得不尝试各种正则表达式字符串：

搜索字符串

([a-zA-Z0-9/,.@():;\s]+)(\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([a-zA-Z0-9/,.@():;\s]+)(\r\n\r\n\r\n\r\n)([a-zA-Z0-9/,.@():;"\s]+)
([\w/,.@():;\s']+)(\r\n)([\w/,.@():;"\s]+)
(;")(\r\n)(";)
(;")(\r\n)([\w/,.@():;\s']+)
(\w")(\r\n)([\w/,.@():;\s']+)

代替

\1@@\3

这个解决方案不是很有效也不是很有效，因为它需要多次运行和数小时来整理 CSV 文件。

问题

如何在导出到 CSV 平面文件期间替换ntext列中的 CR/LF？@@有比REPLACE我可以使用的不同的选择吗？

限制/观察

  • 尚未安装集成服务，因此这不是一个选项。
  • ntext 字符串可以包含", 'and “, ”, ‘，’当然还有可选的«and »。
  • 我已经使用语句分析了该ntext列中的数据，并收到一条记录包含5'562个字符的反馈。CommentSELECT MAX(DATALENGTH(Comment)) FROM Pictures;

我创建了一个db<>fiddle，其中包含表的基本定义和示例数据以及失败的REPLACE()语句。

sql-server sql-server-2014

3 个回答

  1. Best Answer

    SQL Server 的字符串处理函数在处理长字符串时有点不一致，但如果您注意确保要搜索的输入是，NVARCHAR(MAX)那么该REPLACE()函数将处理超过 8,000 个字节（“N”类型为 4,000 个字符）的数据。这意味着您可以NTEXT通过将它们转换为 NVARCHAR(MAX) 并再次返回来操作长值，而不会被截断，如下所示：

    INSERT @t(tt) 
    SELECT CAST(
              REPLICATE(CAST(N'x' AS NVARCHAR(MAX)), 16000) -- Note: for replicate to work as expected make sure its input is explicitly a long type
            + N'☠'
        AS NTEXT)
SELECT DATALENGTH(tt) FROM @t -- 32,002 data length so we have more than the limit of non-MAX character types
-- Now test working with the long value:
SELECT DATALENGTH(CAST(REPLACE(CAST(tt AS NVARCHAR(MAX)),'x','y') AS NTEXT)) FROM @t 
-- still 32,002, has not been truncated
SELECT DATALENGTH(CAST(REPLACE(CAST(tt AS NVARCHAR(MAX)),'x','yz') AS NTEXT)) FROM @t 
-- 64,002
SELECT RIGHT(CAST(CAST(REPLACE(CAST(tt AS NVARCHAR(MAX)),'x','yz') AS NTEXT) AS NVARCHAR(MAX)), 10) FROM @t 
-- paranoia check, we've not lost the non-ASCII character at the end through truncation or conversion

    但这不会特别有效，特别是如果您在这些NTEXT列中有真正的长值。

    因此，对于您的 db-fiddle 示例：

    select REPLACE(CAST(Comment AS NVARCHAR(MAX)), CHAR(10), '@@') from Pictures;

    请注意，小提琴已将您的双字节 EOL 替换为CHAR(10). 此外，如果接收应用程序期望这样，您可能需要转换回NTEXT（如我之前的示例中所做的那样），例如，如果 SSIS 在期望旧 blob 类型时收到正常的长字符串值，那么它肯定会抱怨。

    • 8

  2. 如果你可以使用 CLR 函数，你可以使用这个：

    [Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = -1)]
public static SqlString fnReplace(SqlString value, SqlString oldValue, SqlString newValue)
{
    return new SqlString(((string)value).Replace(oldValue.ToString(), newValue.ToString()));
}

    使用您的示例数据，结果如下：

    DECLARE @crlf NVARCHAR(2) = NCHAR(13) + NCHAR(10);
SELECT dbo.fnReplace(Comment, @crlf, N'@@') FROM Pictures;

Zwei Fotos von Arenenberg auf einer Seite einer englischen Zeitschrift.@@Seite 148 der Zeitung "The Graphic" vom 4. August 1906 = News from Abroad.@@"The last stage of all": the retreat for aged actors opened last week near Meaux, in France@@1. General view of the home@@2. M. Coquelin reciting in the open-air theater@@@@The château of Arenenberg which has been presented by the Empress Eugénie to the canton of Thurgovie@@3. View from the chateau [Arenenberg] over Lake Constance@@4. The château of Arenenberg@@The Empress Eugénie has presented to the Swiss Canton of Thurgovie the historical château of Arenenberg, where Napoleon III. passed several years of his youth. Queen Hortense, on the fall of the first Empire, fled to Switzerland, and in 1817 purchased the castle, which is delightfully situated on the shore of Lake / Constance. The gift includes a priceless collection of paintings, manuscripts, books, old furniture, and tapestries, among the mos important souvenirs being the camp bed of Napoleon III., and the carriage in which he left Sedan after his defeat. When the alterations are complete the château will be opened to the public.@@@@5. The maiden voyage of the new Santos-Dumont flying machine@@6. The room in Viborg where the dissolved Duma met@@

    然后我尝试使用大于 4000 个字符的文本：

    DECLARE @myText NVARCHAR(MAX);
SET @myText  = REPLICATE(N'A', 2000) + @crlf;
set @myText += REPLICATE(N'B', 2000) + @crlf;
SET @myText += REPLICATE(N'C', 1000) + @crlf;

INSERT INTO Pictures VALUES (2, @myText);

    它也有效：

    AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA@@BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB@@CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC@@

    我不能使用你的小提琴，因为我不能向它添加 CLR 函数。

    • 3

  3. 该问题使用 SQL Server 2016 标记。这意味着您有FOR JSON。JSON 编码可以正确转义特殊字符，包括换行符。如果消费者可以接受 JSON，那么您就完成了。如果没有一点 PowerShell / Python / 任何东西都可以用 替换\r\n并@@转换为 CSV，比 TSQL 更容易。

    这是设置。我已经复制了问题中的所有特殊字符并插入了换行符。

    drop table if exists dbo.t;
create table dbo.t(i int, nt ntext);

insert dbo.t(i, nt) select 1, N'z';
insert dbo.t(i, nt) select 2, N' ", '' and “, ”, ‘, ’ and of course'+ CHAR(13) + CHAR(10) + ' the optional « and ».';

select * from dbo.t;

    哪个产生（在 SSMS 中，结果到文本）

    i           nt
----------- ----------------------------------
1           z
2            ", ' and “, ”, ‘, ’ and of course
 the optional « and ».

(2 rows affected)

    所以我们看到换行符在输出中正确表示。与 JSON 相同的数据是

    select * from dbo.t for json auto;

JSON_F52E2B61-18A1-11d1-B105-00805F49916B
-----------------------------------------------------------------------------------------------
[{"i":1,"nt":"z"},{"i":2,"nt":" \", ' and “, ”, ‘, ’ and of course\r\n the optional « and »."}]

(2 rows affected)

    这可以使用 bcp 发送到文件

    bcp "select * from dbo.t for json auto" queryout c:\Development\dumpfile.json -S MyServer\TheInstance -T -d SomeDatabase -c
    • 2

相关问题