>替换ntext数据类型字段中的子字符串 create procedure p_replacentext(@vTable varchar(50), @vField varchar(30),@vBefore varchar(8000),@vAfter varchar(8000)) as begin Declare @Sql nvarchar(4000) declare @p_s varchar(800) Declare @p binary(16),@postion int,@rplen int set @Sql=N'Select @p=textptr('+@vField+'),@rplen=len('''+@vBefore+'''),@postion=patindex(''%'+@vBefore+'%'','+@vField+')-1 from '+@vTable exec sp_executesql @Sql,N'@p binary(16) output,@rplen int output,@postion int output ',@p output,@rplen output,@postion output while @postion >= 0 begin exec master.dbo.xp_varbintohexstr @p, @p_s out set @Sql=N'updatetext '+@vTable+'.'+@vField+' '+@p_s+' '+cast(@postion as varchar(8))+' '+cast(@rplen as varchar(4))+' '''+@vAfter+'''' exec(@Sql) set @Sql=N'select @postion=patindex(''%'+@vBefore+'%'','+@vField+')-1 from '+@vTable exec sp_executesql @Sql,N'@postion int output ',@postion output end end
select replace(cast(e as nvarchar(4000)),N' ',N'' )
from @s别忘了加N
表示uniocde
{ NULL | insert_offset }
{ NULL | delete_length }
[ WITH LOG ]
[ inserted_data
| { table_name.src_column_name src_text_ptr } ]
create procedure p_replacentext(@vTable varchar(50), @vField varchar(30),@vBefore varchar(8000),@vAfter varchar(8000))
as
begin
Declare @Sql nvarchar(4000)
declare @p_s varchar(800)
Declare @p binary(16),@postion int,@rplen int
set @Sql=N'Select @p=textptr('+@vField+'),@rplen=len('''+@vBefore+'''),@postion=patindex(''%'+@vBefore+'%'','+@vField+')-1 from '+@vTable
exec sp_executesql @Sql,N'@p binary(16) output,@rplen int output,@postion int output ',@p output,@rplen output,@postion output
while @postion >= 0
begin
exec master.dbo.xp_varbintohexstr @p, @p_s out
set @Sql=N'updatetext '+@vTable+'.'+@vField+' '+@p_s+' '+cast(@postion as varchar(8))+' '+cast(@rplen as varchar(4))+' '''+@vAfter+''''
exec(@Sql)
set @Sql=N'select @postion=patindex(''%'+@vBefore+'%'','+@vField+')-1 from '+@vTable
exec sp_executesql @Sql,N'@postion int output ',@postion output
end
end