Replace ntext类型中的文字

2007-09-17 18:37

create table #temp (rowid int,textcol ntext) 
insert into   #temp values (1,'aaa bbb ccc ddd eee') 
insert into   #temp values (2,'aaa bbb cc ddd eee') 
insert into   #temp values (3,'fff ggg ccc iii jjj') 
declare @from nvarchar(100), @to nvarchar(100), @pos int, @len int, @rowid int 
declare @ptrval binary(16) 
set @from='ccc' 
set @to='hhh' 
set @len = len(@from) 
set @rowid = 0 
select @rowid = rowid,@pos = charindex(@from,textcol)-1 from #temp where 
charindex(@from,textcol) > 0 
while (@rowid > 0) 
begin 
select @ptrval = textptr(textcol) from #temp where rowid = @rowid 
updatetext #temp.textcol @ptrval @pos @len @to 
set @rowid = 0 
select @rowid = rowid,@pos = charindex(@from,textcol)-1 from #temp where 
charindex(@from,textcol) > 0 
end 
select * from #temp 
drop table #temp