Search an replace text in column for Column Type Text SQL Server

Question

What I need is to search for a string in a specific column (datatype: text) of a table and replace it with another text.

For example

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

If I chose to replace test with quiz, results should be

this is quiz
that is quizosterone

What I've tried so far?

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

I get the error:

Msg 7116, Level 16, State 4, Procedure SearchAndReplace, Line 31
Offset 1900 is not in the range of available LOB data.
The statement has been terminated.

Thank you

Answer 1

If you can't change your column types permanently, you can cast them on the fly:

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

The datatype TEXT is deprecated and should not be used anymore - exactly because it's clunky and doesn't support all the usual string manipulation methods.

From the MSDN docs on text, ntext, image:

ntext, text, and image data types will be removed in a future version of MicrosoftSQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

My recommendation: convert that column to VARCHAR(MAX) and you should be fine after that!

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

That should do it.

When your column is VARCHAR(MAX), then your stored procedures becomes totally simple:

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

Two observations on the side:

• it would be helpful to have a WHERE clause in your stored proc, in order not to update the whole table (unless that's what you really need to do)

• you're using TEXT in your table, yet your stored procedure parameters are of type NVARCHAR - try to stick to one set - either TEXT/VARCHAR(MAX) and regular VARCHAR(100) parameters, or then use all Unicode strings: NTEXT/NVARCHAR(MAX) and NVARCHAR(100). Constantly mixing those non-Unicode and Unicode strings is a mess and causes lots of conversions and unnecessary overhead