declare @s varchar(2000)
set @S='1;1,3,5&2;2,3,5&3;1,3'
declare @t table( a int, b int)
insert into @t
select 1,1
union all
select 1,3
union all
select 1,5
union all
select 2,2
union all
select 2,3
union all
select 2,5
union all
select 3,1
union all
select 3,3select * from @t意思是:
1、根据 & 分组
2、然后根据";" 拆成两个字段。
3、如果后面字段很多。就要再循环拆,请问有没简单办法呢??
set @S='1;1,3,5&2;2,3,5&3;1,3'
declare @t table( a int, b int)
insert into @t
select 1,1
union all
select 1,3
union all
select 1,5
union all
select 2,2
union all
select 2,3
union all
select 2,5
union all
select 3,1
union all
select 3,3select * from @t意思是:
1、根据 & 分组
2、然后根据";" 拆成两个字段。
3、如果后面字段很多。就要再循环拆,请问有没简单办法呢??
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i int set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr) while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end if @inputstr <> '\'
insert @temp values(@inputstr) return
end
go --调用 declare @s varchar(1000) set @s='1,2,3,4,5,6,7,8,55' select * from dbo.fn_split(@s,',') drop function dbo.fn_split