SQL 2000對text類型的處理(分類相加字串)

/*

附上可對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