22,209
社区成员
发帖
与我相关
我的任务
分享
/*
附上可對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
if not object_id('tb') is null
drop table tb
go
Create table tb([name] nvarchar(10),[arrid] ntext)
Insert tb select 'a', '1,2,3,4'
Insert tb select 'b', '6,7,8,9'
Insert tb select 'b', '20,23,24'
Insert tb select 'a', '30,31,32'
create function getstr(@name nvarchar(10))
returns varchar(8000)
as
begin
declare @s varchar(8000)
select @s=isnull(@s+',','')+cast(arrid as varchar(8000)) from tb where name=@name
return @s
end
go
select distinct name,dbo.getstr(name) arrid from tb
/*
name arrid
---- ---------------------
a 1,2,3,4,30,31,32
b 6,7,8,9,20,23,24
(2 行受影响)
*/
drop function dbo.getstr
drop table tb