ntext字段的替换处理示例--全表替换

Mission 1 : 根据需要，将数据库存储文章内容的字段中的域名 www.a.com.cn 替换为 www.b.com.cn。

 

    如此典型的问题 先看SQL server的联机手册，

USE Northwind
GO
CREATE TABLE TextParts (ColA INT PRIMARY KEY, ColB TEXT)
GO
INSERT INTO TextParts
   VALUES( 1,
           'Sample string START TAG Text to go END TAG Trailing text.')
GO
DECLARE @PtrVar BINARY(16)
DECLARE @InsertPos INT
DECLARE @DeleteLen INT

 

SELECT @PtrVar = TEXTPTR(ColB),
       @InsertPos = (PATINDEX('%START TAG%', ColB) + 9),
       @DeleteLen = (
                      PATINDEX('%END TAG%', ColB) -
                      ( PATINDEX('%START TAG%', ColB) + 9
                              + 2 /* allow for blanks */ )
                    )
FROM TextParts
WHERE ColA = 1

 

UPDATETEXT TextParts.ColB
           @PtrVar
           @InsertPos
           @DeleteLen
           WITH LOG
           'The new text'
GO

 

SELECT * FROM TextParts
GO

 

由最后的 SELECT 语句得出的结果集为：

 

ColA        ColB
----------- ------------------------------------------------------------
1           Sample string START TAG The new text END TAG Trailing text.

看来替换的问题解决了，批量替换可能有点麻烦，google吧, 有了http://www.kaidianle.com/article/list16537.html

declare @old varchar(100),@new varchar(100)
set @old='/PicDB/Collection.aspx'
set @new='/PicDB/AlbumView.aspx'
declare @ptr varbinary(16)
declare @newsid int
declare @Position int,@len int
set @len=datalength(@old)

declare wux_Cursor scroll Cursor
for
select textptr([news_content]),[newsid] from music_news
where charindex(@old,news_content)>0
   --and newsid=3093
for read only

open wux_Cursor
fetch next from wux_Cursor into @ptr,@newsid
while @@fetch_status=0
begin
    select @Position=patindex('%' + @old + '%',[news_content]) from music_news where [newsid]=@newsid
    while @Position>0
    begin
    set @Position=@Position-1
     updatetext music_news.[news_content] @ptr @Position @len @new
     select @Position=patindex('%' + @old + '%',[news_content]) from music_news where [newsid]=@newsid
    end
    fetch next from wux_Cursor into @ptr,@newsid
end
close wux_cursor
deallocate wux_cursor
go

用上游标了，不过可以参考了。修改后，很好用！Mission Complete

    declare @old varchar(100),@new varchar(100)

set @old='www.a.com.cn'
set @new='www.b.com.cn'

declare @ptr varbinary(16)
declare @newsid int
declare @Position int,@len int

set @len=datalength(@old)

declare wux_Cursor scroll Cursor
for
 select textptr(content),ArticleID from PE_Article
 where charindex(@old,content)>0 and channelID=1017
 for read only
 open wux_Cursor
 fetch next from wux_Cursor into @ptr,@newsid
 while @@fetch_status=0
 begin
     select @Position=patindex('%' + @old + '%',content) from PE_Article where ArticleID=@newsid
     while @Position>0
     begin
     set @Position=@Position-1
      updatetext PE_Article.content @ptr @Position @len @new
      select @Position=patindex('%' + @old + '%',content) from PE_Article where ArticleID=@newsid
     end
     fetch next from wux_Cursor into @ptr,@newsid
 end
close wux_cursor
deallocate wux_cursor
go

又找了找，还是邹捷的不错，呵呵

--ntext字段的替换处理示例--全表替换

--测试数据
create table test(id varchar(3),txt ntext)
insert into test
select '1','abc我是中a国人
deaf;sdakj21432
我要处理中文'
go

--定义替换的字符串
declare @s_str varchar(8000),@r_str varchar(8000)
select @s_str='
' --要替换的字符串
,@r_str='<P>'--替换成的字符串

--替换处理
declare @id int,@ptr varbinary(16)
declare @start int,@s nvarchar(4000),@len int
declare @s_str1 nvarchar(4000),@s_len int,@i int,@step int

select @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 end

declare tb cursor local for
select id,start=charindex(@s_str,[txt])-1
from [test]
where charindex(@s_str,[txt])>0
--这里可以定义要处理的记录的条件

open tb
fetch tb into @id,@start
while @@fetch_status=0
begin
select @ptr=textptr([txt])
,@s=substring([txt],@start+1,@step)
from [test]
where id=@id

while len(@s)>=@s_len
begin
select @len=len(@s),@i=charindex(@s_str1,reverse(@s))
if @i>0
begin
select @i=case when @i>=@s_len then @s_len else @i end
,@s=replace(@s,@s_str,@r_str)
updatetext [test].[txt] @ptr @start @len @s
end
else
set @i=@s_len
select @start=@start+len(@s)-@i+1
,@s=substring([txt],@start+1,@step)
from [test]
where id=@id
end
fetch tb into @id,@start
end
close tb
deallocate tb
go

--显示处理结果
select * from test
go

--删除测试
drop table test

/*--测试结果

id   txt                                           
---- -----------------------------------------------
1    abc我是中a国人<P>deaf;sdakj21432<P>我要处理中文

（所影响的行数为 1 行）
--*/