存储过程实现:Split字符串获得数组的数据集合
问题提出:在存储过程中如何将字符串:"A,B"和"12,23"中的字符按照","分割出来,并转换成数据集。
解决方法:
CREATE PROCEDURE [dbo].[SplitNamesAndLevels]
@Names nvarchar(max),
@Levels nvarchar(max),
@Tag nvarchar(32) --分割符
as
begin
declare @Table table([Name] nvarchar(255),[Level] int)
declare @length int
begin
declare @i int
declare @j int
declare @SN nvarchar(max)
declare @SL nvarchar(max)
set @length = 0
set @SN = rtrim(ltrim(@Names))
set @SL = rtrim(ltrim(@Levels))
set @i = charindex(@Tag,@SN)
set @j = charindex(@Tag,@SL)
while @i >= 1 and @j >= 1
begin
set @length = @length + 1
insert @Table([Name],[Level])
values(
left(@SN,@i - 1),
CAST(left(@SL,@j - 1) as int)
)
set @SN = substring(@SN,@i + 1,len(@SN) - @i)
set @SL = substring(@SL,@j + 1,len(@SL) - @j)
set @i = charindex(@Tag,@SN)
set @j = charindex(@Tag,@SL)
end
if @SN <> '' and @SL <> ''
begin
set @length = @length + 1
insert @Table values(@SN,CAST(@SL as int))
end
end
select * from @Table
end
如何使用:
当另一个存储过程调用的时候可以这样来获取数据集。
declare @Names nvarchar(max)
declare @Levels nvarchar(max)
declare @Tag nvarchar(32)
set @Name = 'A,B'
set @Levels ='12,23'
set @Tag = ','
declare @TempTable table(Name nvarchar(255),Level int) --声明临时表
insert @TempTable exec SplitNamesAndLevels @Names,@Levels,@Tag