[SQL]代码
01 | create procedure replace_ntext |
02 | @table_name nvarchar(100), --目标表名 |
03 | @field_name nvarchar(100), --目标字段名 |
04 | @where_str nvarchar(1000), --该表主键字段名 如id=1 |
05 | @old nvarchar(1000), --需要替换的字符串 |
06 | @new nvarchar(1000) --替换后的字符串 |
07 | as |
08 | begin |
09 | |
10 | declare @SqlStr nvarchar(4000) |
11 | set @SqlStr = 'declare @pos int, @len int, @count int set @count=0 set @len=len(' '' +@old+ '' ') ' |
12 | set @SqlStr = @SqlStr + ' select @pos = patINDEX(' '%' +@old+ '%' ',[' +@field_name+ ']) - 1' + ' from [' +@table_name+ '] where ' + @where_str |
13 | set @SqlStr = @SqlStr + ' while @pos>0 ' |
14 | set @SqlStr = @SqlStr + ' begin' |
15 | set @SqlStr = @SqlStr + ' declare @ptrval binary(16)' |
16 | set @SqlStr = @SqlStr + ' SELECT @ptrval = TEXTPTR([' +@field_name+ ']) from [' +@table_name+ '] where ' + @where_str |
17 | set @SqlStr = @SqlStr + ' UPDATETEXT [' +@table_name+ '].[' +@field_name+ '] @ptrval @pos @len ' '' + @new + '' '' |
18 | set @SqlStr = @SqlStr + ' select @pos = patINDEX(' '%' +@old+ '%' ',[' +@field_name+ ']) - 1' + ' from [' +@table_name+ '] where ' + @where_str |
19 | set @SqlStr = @SqlStr + ' set @count=@count+1 ' |
20 | set @SqlStr = @SqlStr + ' end ' |
21 | set @SqlStr = @SqlStr + ' select @count' |
22 | --print @SqlStr |
23 | execute sp_executesql @SqlStr |
24 | end |