/* deal with LOB in SQL SERVER */declare @ptrtext varbinary(16) begin tran select @ptrtext = TEXTPTR(r1) from t(UPDLOCK) where id = 1; if @ptrText is not null UPDATETEXT t.r1@ptrtext 0 NULL 'This is a try' commit tran /* end */不懂的可以看SQL SERVER onlinebook
可以实现的,以下是我写的一个例子,将bc改为bbcc。--drop table t --gocreate table t(txt text) insert into t values('12abcdefg') declare @ptr varbinary(16) select @ptr = TEXTPTR(txt) from t--将bc改为bbcc UPDATETEXT t.txt @ptr 4 2 'bbcc' --4表示从第四个字符后开始替换,2表示替换原来的两个位置,'bbcc'是替换的新字串 go select * from t
我写错了!看看谁改改!create table #t(a int,r1 text) insert #t values(1,'acbekih;xaxv') insert #t values(2,'jloasdkjnva;ia') insert #t values(3,'-kdlf-2kjcvn,.') insert #t values(4,'asdjf0a9u23jlkfa-') insert #t values(5,'asdfan,mnxcvn0IOAS') insert #t values(6,'ASDFDSAJiadfiap') insert #t values(7,'alkjlk')declare @ptr varbinary(16),@len int,@i int set @i=(select max(a) from #t) while (@i>0) begin select @len=datalength(r1),@ptr=TEXTPTR(r1) from #t where a=@i if @@rowcount>0 begin declare @bb int,@cc int select @bb=0 select @cc=charindex('a',r1) from #t where a=@i while @cc>0 begin set @bb=@cc+1 select @cc=charindex('a',r1,@bb) from #t where a=@i updatetext #t.r1 @ptr @cc 1 'c' end
end set @i=@i-1 endselect * from #t go drop table #t
DECLARE @ptrval binary(16) SELECT @ptrval = TEXTPTR(pr_info) FROM pub_info pr, publishers p WHERE p.pub_id = pr.pub_id AND p.pub_name = 'New Moon Books' UPDATETEXT pub_info.pr_info @ptrval 88 1 'b' 结合PATINDEX的文本检索能力进行位置查找
to tj_dns(愉快的登山者) 谁说只能进行长度不变的修改???UPDATETEXT pub_info.pr_info @ptrval 0 0 'your text' 就可以实现插入操作了..UPDATETEXT pub_info.pr_info @ptrval x 0 'your text' 这里你只要找到x的正确位置就OK了,而这一点你可以通过PATINDEX去定位
declare @ptrval BINARY(16); declare @position int; declare @length int; SELECT @ptrval=TEXTPTR(LSD_Comment),@length=datalength(LSD_Comment),@position=PATINDEX('%a%',LSD_Comment) FROM Legal_Dispute WHERE LD_Record_No=25; UPDATETEXT Legal_Dispute.LSD_Comment @ptrval @position 0 N'No Change By angle on 3/26/2003 11:16:36 AM ' SELECT LSD_Comment FROM Legal_Dispute WHERE LD_Record_No=25;
begin tran
select @ptrtext = TEXTPTR(r1) from t(UPDLOCK)
where id = 1;
if @ptrText is not null
UPDATETEXT t.r1@ptrtext 0 NULL 'This is a try'
commit tran
/* end */不懂的可以看SQL SERVER onlinebook
但寻找位置也是很麻烦的,不如先将数据读出来,然后修改后再写入。
--gocreate table t(txt text)
insert into t values('12abcdefg')
declare @ptr varbinary(16)
select @ptr = TEXTPTR(txt) from t--将bc改为bbcc
UPDATETEXT t.txt @ptr 4 2 'bbcc'
--4表示从第四个字符后开始替换,2表示替换原来的两个位置,'bbcc'是替换的新字串
go
select * from t
而且在一个字段当中要替换好几个字符串,也就是说要替换好几次,怎么办?
要是字符串替换的话还可以用replace(replace(a,'cc','dd'),'vv','mm')之类,
不知道对于text类型该如何处理?
看来还挺麻烦的,难道在处理text类型的数据时只能这样?
insert #t values(1,'acbekih;xaxv')
insert #t values(2,'jloasdkjnva;ia')
insert #t values(3,'-kdlf-2kjcvn,.')
insert #t values(4,'asdjf0a9u23jlkfa-')
insert #t values(5,'asdfan,mnxcvn0IOAS')
insert #t values(6,'ASDFDSAJiadfiap')
insert #t values(7,'alkjlk')declare @ptr varbinary(16),@len int,@i int
set @i=(select max(a) from #t)
while (@i>0)
begin
select @len=datalength(r1),@ptr=TEXTPTR(r1) from #t where a=@i
if @@rowcount>0
begin
declare @bb int,@cc int
select @bb=0
select @cc=charindex('a',r1) from #t where a=@i while @cc>0
begin
set @bb=@cc+1
select @cc=charindex('a',r1,@bb) from #t where a=@i
updatetext #t.r1 @ptr @cc 1 'c'
end
end
set @i=@i-1
endselect * from #t
go
drop table #t
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr, publishers p
WHERE p.pub_id = pr.pub_id
AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @ptrval 88 1 'b'
结合PATINDEX的文本检索能力进行位置查找
这里你只要找到x的正确位置就OK了,而这一点你可以通过PATINDEX去定位
declare @position int;
declare @length int;
SELECT @ptrval=TEXTPTR(LSD_Comment),@length=datalength(LSD_Comment),@position=PATINDEX('%a%',LSD_Comment) FROM Legal_Dispute WHERE LD_Record_No=25;
UPDATETEXT Legal_Dispute.LSD_Comment @ptrval @position 0 N'No Change By angle on 3/26/2003 11:16:36 AM '
SELECT LSD_Comment FROM Legal_Dispute WHERE LD_Record_No=25;