Replace a string in an NTEXT field in sql server

I found it rather hard to replace a string in an NTEXT field in sql server 2000. Would it be easier in SSIS 2005? Please advise. Thanks.

NTEXT is depricated in SQL 2005 and was replaced by NVARCHAR(MAX). You can use the REPLACE in SQL 2005

http://msdn2.microsoft.com/en-us/library/ms186862.aspx

|||Can access a sql2000 table with NTEXT field as it is and replace a string in that field using SSIS2005? Please advise. Thanks.|||

As I know in SQL2000 the REPLACE will not accept NTEXT data as parameter, which brings trouble when update TEXT data. If there are less than 4000 chars in the NTEXT column, I'd suggest casting the NTEXT data to NVARCHAR(4000) data so that you can use the REPLACE function. Otherwise the replacing is really ugly. Here is a sample to update TEXT data by replacing string:

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;

|||

Does your solution apply only if there are less than 4000 chars in the NTEXT column? Here's my next questions: how can I find the max length of those ntext fields? But if the max length exceeds 4000, can I use nvarchar(max) in the sql2005 temp table and, after replacing my string, convert the result back to ntext in the sql 2000 source table? Can I use sql2005 for temp work and keep the final data in sql2000. Thanks.

|||You can use datalength(yourColumn) to find your ntext length.|||lori_Jay's solution looks good but has a serious flaw: the new string replacement is truncated to the old string's length after it goes into the NTEXT field. Please advise. Thanks.|||

Problem can be solved with repalcing:
UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex @.lenOldString @.newString;
with
UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex @.lenOldString -- deletes old string
UPDATETEXT dbo.data.txt @.TextPointer @.TextIndex 0 @.newString -- inserts new string