/*
附上可對ntext,text,image操作的sql2000 function
datalength,readtext,patindex,
set textsize,substring,updatetext,textptr,writetext,textvalid
以下是對text類型欄位的“字串相加”,方法不好.......
----2007.11.30 by playwarcraft----
*/
create table T(id int,col text)
insert into T select 1, 'edf'
insert into T select 1, 'ghi'
insert into T select 2, 'zysop##***'
insert into T select 1,'HHH'
insert into T select 2,'KKK'
GO
create table #t1(id int,col text)
create table #t2(id int,col text)
declare @t table (col varchar(8000))
declare @id int, @n int,@i int ,@p binary(16),@len int, @col varchar(8000)
select @n=max(datalength(col))/8000+1 from T
declare c1 cursor for
select distinct id from T
open c1
fetch next from c1 into @id
while @@fetch_status=0
BEGIN
set @i=0
while @n>@i
begin
insert into @t select substring(col,(@i)*8000,8000) from T where id=@id
set @i=@i+1
end
insert into #t1 select @id,''
declare c2 cursor for
select col from @t where col<>''
open c2
fetch next from c2 into @col
while @@fetch_status=0
begin
select @p=textptr(col),@len=datalength(col) from #t1
updatetext #t1.col @p @len 0 @col
fetch next from c2 into @col
end
close c2
deallocate c2
insert into #t2 select * from #t1
truncate table #t1
delete @t
fetch next from c1 into @id
END
close c1
deallocate c1
GO
select * from #t2
/*
id col
------------------------------
1 edfghiHHH
2 zysop##***KKK
*/
GO
drop table T,#t1,#t2