Replace and ntext

I need replace a string in a ntext field.
Any ideas ?
Tks.check following example on pubs database . This will create a new table
pub_info_1 with the replaced data of pub_info table. Following example will
work for TEXT datatype for NTEXT, probably you will have to take care of
datalength which is generally datalength/2 (because unicode takes 2 bytes to
store a character.)
(Take a copy of the actual table before trying anything.)
DECLARE @.orig_str varchar(8000), @.rep_str varchar(8000)
SET NOCOUNT ON
DECLARE @.y int,@.str varchar(8000),@.dtlen int, @.pub_id int
DECLARE @.ptrval binary(16),@.ptrval1 binary(16)
SELECT @.orig_str='new moon books', --old string
@.rep_str='old moon books' --new string
--rep_str should not be greater than 100
SELECT @.pub_id = 0
IF object_id('pub_info_1') is not null
DROP table pub_info_1
CREATE table pub_info_1 (new_txt text)
if object_id('tempdb..#txt_1') is not null
drop table #txt_1
CREATE table #txt_1 (xx text)
WHILE @.pub_id is not null
BEGIN
TRUNCATE table #txt_1
INSERT into #txt_1 values ('')
SELECT @.ptrval1 = TEXTPTR(xx)
FROM #txt_1
SELECT @.pub_id=min(pub_id)
FROM pub_info where pub_id > @.pub_id
-- FROM pub_info where pub_id = '9999'
SELECT @.ptrval = TEXTPTR(pr_info) , @.y=1, @.dtlen = datalength(pr_info)
FROM pub_info where pub_id = @.pub_id
WHILE 1=1
BEGIN
SELECT @.str= replace (substring(pr_info, @.y, 7900), @.orig_str, @.rep_str)
FROM pub_info where pub_id = @.pub_id
UPDATETEXT #txt_1.xx @.ptrval1 null 0 @.str
SELECT @.y = @.y + 7900
IF @.y > @.dtlen
BEGIN
BREAK
END
END
If @.pub_id is not null
INSERT into dbo.pub_info_1
SELECT * FROM #txt_1
END
DROP TABLE #txt_1
--
-Vishal
SJPiola <sjpiola@.msn.com> wrote in message
news:029401c34fc0$8f102160$a401280a@.phx.gbl...
> I need replace a string in a ntext field.
> Any ideas ?
> Tks.