删除MSSQL数据库text字段的替换处理示例--全表替换,看到有人提问,所以整理了一个好久以前的处理方法,以供大家参考:
方法很简单:text字段不能使用Replace,所以使用patindex
-
select
*
from
Product
where
P_Intro
like
'
%<script src=http://my.stsw518.cn/a002/1.js></script>%
'
-- text字段的替换处理示例--全表替换
-- select datalength(P_Intro),* from Product
-- 邀月 整理
-- 定义替换的字符串
declare @s_str nvarchar ( 4000 ), @d_str nvarchar ( 4000 )
select @s_str = ' <script src=http://my.stsw518.cn/a002/1.js></script> ' -- 要替换的字符串
, @d_str = '' -- 替换成的字符串
-- 因为只能用patindex,所以对于搜索字符串做处理
set @s_str = ' % ' + @s_str + ' % '
-- 定义游标,循环处理数据
declare @id bigint
declare #tb cursor for select P_ID from Product where P_Intro like ' %<script src=http://my.stsw518.cn/a002/1.js></script>% '
-- where P_ID=300727 ----where P_Intro like '%<script src=http://my.stsw518.cn/a002/1.js></script>%'
open #tb
fetch next from #tb into @id
while @@fetch_status = 0
begin
-- 字符串替换处理
declare @p varbinary ( 16 )
, @p1 int , @p2 int
, @rplen int , @step int , @len int
select @p = textptr (P_Intro)
, @rplen = len ( @s_str ) - 2
, @step = len ( @d_str )
, @p1 = patindex ( @s_str ,P_Intro)
, @len = datalength (P_Intro)
, @p2 = 0
from Product
where P_id = @id
while @p1 > 0
begin
set @p2 = @p1 + @p2 - 1
updatetext Product.P_Intro @p @p2 @rplen @d_str
select @p2 = @p2 + 1 , @p1 = patindex ( @s_str , substring (P_Intro, @p2 + 1 , @len ))
from Product where P_ID = @id
end
fetch next from #tb into @id
end
close #tb
deallocate #tb
-- 显示结果
-- -- select datalength(P_Intro),* from Product
-- text字段的替换处理示例--全表替换
-- select datalength(P_Intro),* from Product
-- 邀月 整理
-- 定义替换的字符串
declare @s_str nvarchar ( 4000 ), @d_str nvarchar ( 4000 )
select @s_str = ' <script src=http://my.stsw518.cn/a002/1.js></script> ' -- 要替换的字符串
, @d_str = '' -- 替换成的字符串
-- 因为只能用patindex,所以对于搜索字符串做处理
set @s_str = ' % ' + @s_str + ' % '
-- 定义游标,循环处理数据
declare @id bigint
declare #tb cursor for select P_ID from Product where P_Intro like ' %<script src=http://my.stsw518.cn/a002/1.js></script>% '
-- where P_ID=300727 ----where P_Intro like '%<script src=http://my.stsw518.cn/a002/1.js></script>%'
open #tb
fetch next from #tb into @id
while @@fetch_status = 0
begin
-- 字符串替换处理
declare @p varbinary ( 16 )
, @p1 int , @p2 int
, @rplen int , @step int , @len int
select @p = textptr (P_Intro)
, @rplen = len ( @s_str ) - 2
, @step = len ( @d_str )
, @p1 = patindex ( @s_str ,P_Intro)
, @len = datalength (P_Intro)
, @p2 = 0
from Product
where P_id = @id
while @p1 > 0
begin
set @p2 = @p1 + @p2 - 1
updatetext Product.P_Intro @p @p2 @rplen @d_str
select @p2 = @p2 + 1 , @p1 = patindex ( @s_str , substring (P_Intro, @p2 + 1 , @len ))
from Product where P_ID = @id
end
fetch next from #tb into @id
end
close #tb
deallocate #tb
-- 显示结果
-- -- select datalength(P_Intro),* from Product