How do I handle REPLACE() within an NTEXT column in SQL Server?

I recently was confronted with a problem where a piece of text that was included in many NTEXT column values in a table needed to be replaced with another piece of text. You can't issue normal REPLACE statements against NTEXT columns, so this seemed to be a bit of a challenge — issuing a REPLACE() against a TEXT or NTEXT column in SQL Server yields: 
 

Server: Msg 8116, Level 16, State 1, Line 1  Argument data type text is invalid for argument 1 of replace function.

 
And from within an ASP page: 
 

Microsoft OLE DB Provider for SQL Server error '80040e14'   Argument data type text is invalid for argument 1 of replace function.

 
Initially, I thought I would have to resort to writing an app or web page, pull the NTEXT data out of the database, do the replace in VBScript or C#, and update the whole value. I wasn't really looking forward to writing such an app, much less debug it until I knew it would perform acceptably. 
 
UPDATETEXT and a quick and dirty cursor really saved my bacon on this one. As long as you have a unique identifier in the table (e.g. an INDENTITY value or a GUID), you can loop through the table and, row by row, issue UPDATETEXT for each instance of the 'bad' text you want to replace. 
 
This was tested successfully, as is, in SQL Server 2000 and SQL Server 2005. Though, in SQL Server 2005, there's no reason you should be delaying the conversion of your column(s) from TEXT/NTEXT to VARCHAR(MAX)/NVARCHAR(MAX) - these new types are far more flexible and really are, as Erland describes it, "first class citizens" in the SQL Server type world. 
 
If you are using SQL Server 7.0, see the notes below. 
 
Run this script in Query Analyzer to get a feel for it... I will be replacing all instances of 'food' with the word 'fudge'. Do *NOT* try to run this script where @oldString is a subset of (or equal to) @newString, e.g. 'food' replaced by 'food' or 'food poison' ... you will get an infinite loop. I have commented the 5 or 6 lines that need to change if the column is TEXT rather than NTEXT. 
 

USE TempDB;  GO    SET NOCOUNT ON;    CREATE TABLE dbo.data  (      DataID INT PRIMARY KEY,      txt NTEXT -- change to TEXT  );  GO    INSERT dbo.data      SELECT 1, N'bar foodfood food har sammy'      UNION ALL SELECT 2, N'bar sammy food'      UNION ALL SELECT 3, N'bar fooblat sammy'      UNION ALL SELECT 4, N'food';    DECLARE      @TextPointer BINARY(16),      @TextIndex INT,      @oldString NVARCHAR(32), -- change to VARCHAR      @newString NVARCHAR(32), -- change to VARCHAR      @lenOldString INT,      @currentDataID INT;    SET @oldString = N'food'; -- remove N  SET @newString = N'fudge'; -- remove N    IF CHARINDEX(@oldString, @newString) > 0   BEGIN      PRINT 'Quitting to avoid infinite loop.';  END  ELSE  BEGIN      SELECT 'Before replacement:';        SELECT DataID, txt FROM data;        SET @lenOldString = DATALENGTH(@oldString)/2; -- remove /2        DECLARE irows CURSOR          LOCAL FORWARD_ONLY STATIC READ_ONLY FOR          SELECT              DataID          FROM              dbo.data          WHERE              PATINDEX('%'+@oldString+'%', txt) > 0;        OPEN irows;        FETCH NEXT FROM irows INTO @currentDataID;        WHILE (@@FETCH_STATUS = 0)      BEGIN            SELECT              @TextPointer = TEXTPTR(txt),               @TextIndex = PATINDEX('%'+@oldString+'%', txt)          FROM              dbo.data          WHERE              DataID = @currentDataID;            WHILE          (              SELECT                  PATINDEX('%'+@oldString+'%', txt)              FROM                  dbo.data              WHERE                  DataID = @currentDataID          ) > 0          BEGIN              SELECT                  @TextIndex = PATINDEX('%'+@oldString+'%', txt)-1              FROM                  dbo.data              WHERE                  DataID = @currentDataID;                UPDATETEXT dbo.data.txt @TextPointer @TextIndex @lenOldString @newString;          END            FETCH NEXT FROM irows INTO @currentDataID;      END        CLOSE irows;        DEALLOCATE irows;        SELECT 'After replacement:';        SELECT DataID, txt FROM data;  END    DROP TABLE dbo.data;

 
(Note that UPDATETEXT does not cause triggers to fire like a normal UPDATE statement would... so you don't have to disable a trigger, for example, responsible for auditing row changes, however take note that the changes will not be recorded if you are using this methodology.) 
 
SQL Server 7.0 
 
With SQL Server 7.0, it appears you have to set the BULKCOPY attribute to true, otherwise you will get the following error: 
 

Server: Msg 7130, Level 16, State 1, Line 47  UpdateText WITH NO LOG is not valid at this time. Use sp_dboption to set the 'select into/bulkcopy' option on for database 'pubs'.

 
You can set the BULKCOPY attribute with the following setting: 
 

EXEC master.dbo.sp_dboption 'pubs', 'BULKCOPY', 'true'

 
(Change to false after you've run the script... you should only be performing this kind of task manually and occasionally, and this is not a setting you want retained permanently.)