随笔- 29  评论- 129  文章- 564 

MSSQL批量替换Text字符串

declare @old varchar(100)
declare @new varchar(100)
set @old='http://new.chaoren.com/CMS/localhost'
set @new='http://www.chaoren.com/CMS/localhost'
declare @ptr varbinary(16)
declare @newsid varchar(36)  --如为int则改为declare @newsid int
declare @Position int,@len int
set @len=datalength(@old)

declare yohen_Cursor scroll Cursor
for
select textptr([content]),[news_id] from t_news_content
where charindex(@old,content)>0 --and news_id='2007-06-08'
for read only

open yohen_Cursor
fetch next from yohen_Cursor into @ptr,@newsid
while @@fetch_status=0
begin
    select @Position=patindex('%' + @old + '%',[content]) from t_news_content where news_id=@newsid
    while @Position>0
    begin
    set @Position=@Position-1
    updatetext t_news_content.[content] @ptr @Position @len @new
    select @Position=patindex('%' + @old + '%',[content]) from t_news_content where news_id=@newsid
    end
    fetch next from yohen_Cursor into @ptr,@newsid
end
close yohen_Cursor
deallocate yohen_Cursor
go
posted on 2009-03-20 13:16 石川 阅读(...) 评论(...) 编辑 收藏