//分割字符串
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Get_StrArrayLength]') and xtype in (N'FN', N'IF', N'TF'))
begin
drop function [dbo].[Get_StrArrayLength]
end
GO
CREATE function [dbo].[Get_StrArrayLength]
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Get_StrArrayStrOfIndex]') and xtype in (N'FN', N'IF', N'TF'))
begin
drop function [dbo].[Get_StrArrayStrOfIndex]
end
GO
CREATE function [dbo].[Get_StrArrayStrOfIndex]
(
@str varchar(1024), --要分割的字符串
@split varchar(10), --分隔符号
@index int --取第几个元素
)
returns varchar(1024)
as
begin
declare @location int
declare @start int
declare @next int
declare @seed int
set @str=ltrim(rtrim(@str))
set @start=1
set @next=1
set @seed=len(@split)
set @location=charindex(@split,@str)
while @location<>0 and @index>@next
begin
set @start=@location+@seed
set @location=charindex(@split,@str,@start)
set @next=@next+1
end
if @location =0 select @location =len(@str)+1
--这儿存在两种情况:1、字符串不存在分隔符号 2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。
return substring(@str,@start,@location-@start)
end
go
declare @id int,@periodRecord varchar(1024)
set @id = (select top(1) id from @Ids)
--用货记录 [格式:'1,53,55,88']
set @periodRecord = (select PeriodRecord from customer where Id = @id)
--调用自定义方法,将记录拆分存放到临时表
declare @next int
declare @temp table(id int)
delete from @temp
--判断记录数是否大于次数
if(dbo.Get_StrArrayLength(@periodRecord,',') >@times)
begin
set @next=dbo.Get_StrArrayLength(@periodRecord,',')-@times+1
end
else
begin
set @next =2 --PeriodRecord 默认值为0
end
while @next<=dbo.Get_StrArrayLength(@periodRecord,',')
begin
Insert into @temp select dbo.Get_StrArrayStrOfIndex(@periodRecord,',',@next)
set @next=@next+1
end