在列中搜索列类型文本 SQL Server 的替换文本

Question

我需要的是在表的特定列（数据类型：text）中搜索字符串并将其替换为另一个文本。

例如

Id          |          Text
-----------------------------
1                   this is test
2                   that is testosterone

，如果我选择用测验替换测试，结果应该是

this is quiz
that is quizosterone

到目前为止我尝试过什么？

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SearchAndReplace] 
(
     @FindString    NVARCHAR(100)
    ,@ReplaceString NVARCHAR(100)
)
AS
BEGIN
    SET NOCOUNT ON
SELECT CONTENT_ID as id, CONTENT_TEXT, textptr(CONTENT_TEXT) as ptr, datalength(CONTENT_TEXT) as lng
 INTO #newtable6  FROM HTML_CONTENTS 
    DECLARE @COUNTER INT = 0
    DECLARE @TextPointer VARBINARY(16) 
    DECLARE @DeleteLength INT 
    DECLARE @OffSet INT 

    SELECT @TextPointer = TEXTPTR(CONTENT_TEXT)
      FROM #newtable6

    SET @DeleteLength = LEN(@FindString) 
    SET @OffSet = 0
    SET @FindString = '%' + @FindString + '%'

    WHILE (SELECT COUNT(*)
             FROM #newtable6
            WHERE PATINDEX(@FindString, CONTENT_TEXT) <> 0) > 0
    BEGIN 
        SELECT @OffSet = PATINDEX(@FindString, CONTENT_TEXT) - 1
          FROM #newtable6
         WHERE PATINDEX(@FindString, CONTENT_TEXT) <> 0

UPDATETEXT #newtable6.CONTENT_TEXT
            @TextPointer
            @OffSet
            @DeleteLength
            @ReplaceString

           SET @COUNTER = @COUNTER + 1
    END
    select @COUNTER,* from #newtable6
drop table #newtable6
    SET NOCOUNT OFF

我收到错误：

消息 7116，级别 16，状态 4，过程 SearchAndReplace，第 31 行
偏移量 1900 不在可用 LOB 数据的范围内。
该声明已终止。

谢谢

Answer 1

如果您无法永久更改列类型，则可以动态转换它们：

ALTER PROC [dbo].[SearchAndReplace] 
(@FindString    VARCHAR(100),
 @ReplaceString VARCHAR(100) )
AS
BEGIN
   UPDATE dbo.HTML_CONTENTS
   SET CONTENT_TEXT = cast (REPLACE(cast (CONTEXT_TEXT as varchar(max)), @FindString, @ReplaceString) as TEXT)
END

Answer 2

数据类型 TEXT 已已弃用 并且不应该再使用 - 正是因为它很笨重并且不支持所有常用的字符串操作方法。

来自关于 text、ntext、image 的 MSDN 文档：

ntext、text 和 image 数据类型将
将在未来版本中删除
微软SQL服务器。 避免使用这些
新开发工作中的数据类型，
并计划修改应用程序
目前正在使用它们。 使用 nvarchar(max),
varchar(max) 和 varbinary(max)
相反。

我的建议：将该列转换为 VARCHAR(MAX)，之后应该就没问题了！

ALTER TABLE dbo.HTML_CONTENTS 
   ALTER COLUMN CONTEXT_TEXT VARCHAR(MAX)

应该可以做到这一点。

当您的列是 VARCHAR(MAX) 时，您的存储过程将变得完全简单：另外

ALTER PROC [dbo].[SearchAndReplace] 
(@FindString    VARCHAR(100),
 @ReplaceString VARCHAR(100) )
AS
BEGIN
   UPDATE dbo.HTML_CONTENTS
   SET CONTENT_TEXT = REPLACE(CONTEXT_TEXT, @FindString, @ReplaceString)
END

两个观察结果：

• 在其中包含一个 WHERE 子句会很有帮助你的存储过程，为了不更新整个表（除非那是你真正需要做的）

• 您在表中使用 TEXT，但存储过程参数的类型为 NVARCHAR - 尝试坚持使用一组 - TEXT/VARCHAR(MAX) 和常规VARCHAR(100) 参数，或者使用所有 Unicode 字符串：NTEXT/NVARCHAR(MAX) 和 NVARCHAR(100)。不断混合这些非 Unicode 和 Unicode 字符串会造成混乱，并导致大量转换和不必要的开销