if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_split]
GO
--用个函数就行了.
create function f_split(@str varchar(8000))
returns @re table(id int identity(1,1),re varchar(1000))
as
begin
declare @tb table(id int identity(1,1),a int)
insert into @tb(a) select top 8000 null from
(select id from syscolumns) as a,(select id from syscolumns) as b
insert into @re(re)
select substring(name,b.id,charindex('_',name+'_',b.id)-b.id)
from (select name=@str) a join @tb b on substring('_'+name,b.id,1)='_'
return
end
go
--调用示例:
select * from dbo.f_split('1_3_4_6_9_23')
/*--结果
id re
----------- ------
1 1
2 3
3 4
4 6
5 9
6 23
declare @string varchar(1000)
declare @xh char(10)
declare @sh varchar(13)
set @string='1_3_4_6_9_23'
set @string='select * into ##tmp from (select '''+replace(@string,'_','''as str union all select ''')+''') a'
exec(@string)
print @string
--N要连继,
select top 8000 identity(int,1,1) as N into numtab from
(select top 100 id=1 from sysobjects) as a,
(select top 100 id=1 from sysobjects) as b,
(select top 100 id=1 from sysobjects) as c
---------------------------------------------------------------------
declare @a table (id int,string varchar(8000))
insert @a select 1 ,'a,b,c,sd,dfsdfg'
union select 2, 'a,n,sdf,we,t'
union select 3, 's,df,df'
select a.*,b.*,id,substring(','+string+',',N+1,charindex(',',','+string+',',N+1)-(N+1))
from @a a,numtab b
where substring(','+string+',',N,8000) like ',_%'
order by id,N