使用 SQL Server 替换功能替换字符串 - 字符串超过 4000 个字符 - 该怎么办? [英] Replacing a string using SQL Server Replace function - string has more than 4000 characters - what to do?

问题描述

我有一个表格，我需要在其中替换列中的一些值.

I have a table, where I need to replace some values in a column.

数据库在 SQL Server 2005 上运行.

The database is running on SQL Server 2005.

问题是某些行包含超过 4000 个字符，这给 REPLACE 函数带来了一些麻烦，因为它要求我将第一个参数转换为数据类型 NVARCHAR，因此任何超过 4000 的字符都会被截断.

The problem is that some of the rows contain more than 4000 characters, which is giving the REPLACE function some trouble, since it demands that I cast the first parameter to the datatype NVARCHAR, and therefore any characters exceeding 4000, is being truncated.

除了编写处理此问题的应用程序之外，还有其他解决方法吗?

Is there any workaround for this, other than writing an application that handles this issue?

有问题的查询是:

SELECT 
   Replace(cast([Database].[dbo].[fruits].[Tekst] as NVARCHAR(MAX)), 'bananas', 'apples') 
FROM [Database].[dbo].[fruits]

fruits 列的数据类型为 Text

感谢任何输入.

推荐答案

我怀疑您刚刚达到了 SSMS 中单个值的限制.

I suspect that you have just hit the limit for an individual value in SSMS.

令人讨厌的是，它不允许您将其设置为无限制，我所知道的显示长文本的唯一方法是通过转换为 XML，如下所示.

Annoyingly it doesn't allow you to set this to be unlimited and the only way I know of displaying long text is via casting to XML as below.

select
(select Replace(cast([Database].[dbo].[fruits].[Tekst] as NVARCHAR(MAX)),'bananas','apples')  AS [processing-instruction(x)] FOR XML PATH(''), TYPE)
FROM [Database].[dbo].[fruits]

这篇关于使用 SQL Server 替换功能替换字符串 - 字符串超过 4000 个字符 - 该怎么办?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！