|
--替换处理declare @s_str nvarchar(4000),@r_str nvarchar(4000)select @s_str='开心'--要替换的字符串,@r_str='你好'--替换成该字符串--替换处理declare @id int,@ptr varbinary(16)declare @start int,@s nvarchar(4000),@len intdeclare @s_str1 nvarchar(4000),@s_len int,@i int,@step intselect @s_str1=reverse(@s_str),@s_len=len(@s_str),@step=case when len(@r_str)>len(@s_str)then 4000/len(@r_str)*len(@s_str)else 4000 enddeclare tb cursor local for select id,start=charindex(@s_str,[content])-1from [tb]where id=2 --替换id=1的记录and charindex(@s_str,[content])>0open tb fetch tb into @id,@startwhile @@fetch_status=0beginselect @ptr=textptr([content]),@s=substring([content],@start+1,@step)from [tb]where id=@idwhile len(@s)>=@s_lenbeginselect @len=len(@s),@i=charindex(@s_str1,reverse(@s))if @i>0beginselect @i=case when @i>=@s_len then @s_len else @i end,@s=replace(@s,@s_str,@r_str)updatetext [tb].[content] @ptr @start @len @sendelseset @i=@s_lenselect @start=@start+len(@s)-@i+1,@s=substring([content],@start+1,@step)from [tb]where id=@idendfetch tb into @id,@startendclose tbdeallocate tbgo |
