/*--text字段的替换处理;;;
--*/;;;
--创建数据测试环境;;;
--create;;;table;;;#tb(aa;;;text)
declare @s_str varchar(8000),@d_str varchar(8000),;;--定义替换的字符串;;
@p varbinary(16),@postion int,@rplen int,@i_Start int, @i_End int

select identity(int,1,1) as [id],newsid into # from news
select @i_Start=min([id]),@i_End=max([id]) from #
while (@i_Start<=@i_End)
begin;;
--insert into #tb(aa) select content from # where [id]=@i_Start
select;;;@s_str='/';;;--要替换的字符串;;;
,@d_str='!' --替换成的字符串;;;;;;
--字符串替换处理
select;;;@p=textptr(content),@rplen=len(@s_str),@postion=charindex(@s_str,content)-1 from news where newsid in (select top 1 newsid from # where [id]=@i_Start);;
while;;;@postion>0;;;
begin;;;
updatetext;;;news.content;;;@p;;;@postion;;;@rplen;;;@d_str;;;
select;;;@postion=charindex(@s_str,content)-1;;from news where newsid in (select top 1 newsid from # where [id]=@i_Start)
end;;
--truncate table #tb

select @i_Start=@i_Start+1
end

--删除数据测试环境;;;
--drop;;;table;;;#tb;;
drop table #;

标签：