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;