--创建数据测试环境 create table xinwen([body] text) insert into xinwen select 'abcwww.sina.cnabcwww.sina.cn,asd' --定义替换的字符串 declare @s_str varchar(8000),@d_str varchar(8000) select @s_str='www.sina.cn' --要替换的字符串 ,@d_str='www.csdn.com' --替换成的字符串--字符串替换处理 declare @p varbinary(16),@postion int,@rplen int select @p=textptr([body]),@rplen=len(@s_str),@postion=charindex(@s_str,[body])-1 from xinwen while @postion>0 begin updatetext xinwen.[body] @p @postion @rplen @d_str select @postion=charindex(@s_str,[body])-1 from xinwen end --结果 body abcwww.csdn.comabcwww.csdn.com,asd(所影响的行数为 1 行)
--创建测试环境 create table xinwen ( ID int, body ntext ) insert xinwen select 1,'BBBBBwww.sina.comAAAAA' insert xinwen select 2,'Aawww.sina.comv' insert xinwen select 3,'Aav'--测试 declare @ptr varbinary(16),@pos int declare cur cursor for select ID from xinwen declare @ID int open cur fetch next from cur into @ID while @@fetch_status=0 begin select @ptr=textptr(body) from xinwen where ID=@ID select @pos=charindex('www.sina.com',body) from xinwen where ID=@ID if @pos<>0 updatetext xinwen.body @ptr @pos 12 'www.csdn.net' fetch next from cur into @ID end close cur deallocate cur--查看 select * from xinwen--删除测试环境 drop table xinwen--结果 /* ID body ----------- --------------------- 1 BBBBBwwww.csdn.netAAAA 2 Aawwww.csdn.net 3 Aav(3 row(s) affected) */
replace是字符串函数,这个函数不能对ntext这个数据类型进行操作。
create table xinwen([body] text)
insert into xinwen select 'abcwww.sina.cnabcwww.sina.cn,asd'
--定义替换的字符串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='www.sina.cn' --要替换的字符串
,@d_str='www.csdn.com' --替换成的字符串--字符串替换处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr([body]),@rplen=len(@s_str),@postion=charindex(@s_str,[body])-1 from xinwen
while @postion>0
begin
updatetext xinwen.[body] @p @postion @rplen @d_str
select @postion=charindex(@s_str,[body])-1 from xinwen
end
--结果
body
abcwww.csdn.comabcwww.csdn.com,asd(所影响的行数为 1 行)
create table xinwen
(
ID int,
body ntext
)
insert xinwen select 1,'BBBBBwww.sina.comAAAAA'
insert xinwen select 2,'Aawww.sina.comv'
insert xinwen select 3,'Aav'--测试
declare @ptr varbinary(16),@pos int
declare cur cursor for select ID from xinwen
declare @ID int
open cur
fetch next from cur into @ID
while @@fetch_status=0
begin
select @ptr=textptr(body) from xinwen where ID=@ID
select @pos=charindex('www.sina.com',body) from xinwen where ID=@ID
if @pos<>0
updatetext xinwen.body @ptr @pos 12 'www.csdn.net'
fetch next from cur into @ID
end
close cur
deallocate cur--查看
select * from xinwen--删除测试环境
drop table xinwen--结果
/*
ID body
----------- ---------------------
1 BBBBBwwww.csdn.netAAAA
2 Aawwww.csdn.net
3 Aav(3 row(s) affected)
*/