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 INTSELECT @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 = 1UPDATETEXT TextParts.ColB
@PtrVar
@InsertPos
@DeleteLen
WITH LOG
'The new text'
GOSELECT * 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.htmldeclare @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 intset @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 intselect @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 enddeclare 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=@idwhile 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 行)
--*/
ntext字段的替换处理示例--全表替换
最新推荐文章于 2021-08-19 11:14:53 发布
Mission 1 : 根据需要,将数据库存储文章内容的字段中的域名
www.a.com.cn 替换为
www.b.com.cn。
如此典型的问题 先看SQL server的联机手册,