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

Mission 1 : 根据需要，将数据库存储文章内容的字段中的域名 www.a.com.cn 替换为 www.b.com.cn。       如此典型的问题 先看SQL server的联机手册， USE NorthwindGOCREATE TABLE TextParts (ColA INT PRIMARY KEY, ColB TEXT)GOINSERT INTO TextParts   VALUES( 1,           'Sample string START TAG Text to go END TAG Trailing text.')GODECLARE @PtrVar BINARY(16)DECLARE @InsertPos INTDECLARE @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 TextPartsWHERE ColA = 1   UPDATETEXT TextParts.ColB           @PtrVar           @InsertPos           @DeleteLen           WITH LOG           'The new text'GO   SELECT * FROM TextPartsGO   由最后的 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 intdeclare @Position int,@len intset @len=datalength(@old)

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

open wux_Cursor fetch next from wux_Cursor into @ptr,@newsidwhile @@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 endclose 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 intdeclare @Position int,@len int

set @len=datalength(@old)

declare wux_Cursor scroll Cursorfor  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  endclose wux_cursor deallocate wux_cursor go

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

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

--测试数据create table test(id varchar(3),txt ntext)insert into testselect '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 intdeclare @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])-1from [test]where charindex(@s_str,[txt])>0--这里可以定义要处理的记录的条件

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

while len(@s)>=@s_lenbeginselect @len=len(@s),@i=charindex(@s_str1,reverse(@s))if @i>0beginselect @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 @sendelseset @i=@s_lenselect @start=@start+len(@s)-@i+1,@s=substring([txt],@start+1,@step)from [test]where id=@idendfetch tb into @id,@startendclose tbdeallocate tbgo

--显示处理结果select * from testgo

--删除测试drop table test

/*--测试结果

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

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