set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Function [dbo].[SpliteStringToListByParam](
@strings varchar(max),
@sp VARCHAR(10)
)
/*
调用示例：SELECT * FROM [SpliteStringToListByParam]('aaa@@@bbb','@@@')
*/
Returns @ReturnTable Table(idx int IDENTITY(1,1) Primary Key,ID VARCHAR(max))
As
BEGIN
DECLARE @len INT
SET @len=len(@sp)+1
Insert @ReturnTable
select substring(c,@len,charindex(@sp,c,@len)-@len) as empno   from
(
select substring(csv.emps,iter.pos,len(csv.emps)) as c from
(
select @sp+@strings+@sp as emps
) csv,
(
--select id as pos from t100
   --生产1-10000的结果集
   Select  a + b * 10 +c*100+d*1000+1 as pos From
(Select 0 As a Union Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5 Union Select 6 Union  Select 7 Union Select 8 Union Select 9 ) A,
(Select 0 As b Union Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5 Union Select 6 Union  Select 7 Union Select 8 Union Select 9 ) B,
(Select 0 As c Union Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5 Union Select 6 Union  Select 7 Union Select 8 Union Select 9 ) c,
(Select 0 As d Union Select 1 Union Select 2 Union Select 3 Union Select 4 Union Select 5 Union Select 6 Union  Select 7 Union Select 8 Union Select 9 ) d
) iter where iter.pos <= len(csv.emps)
)x
where len(c) > len(@sp) and substring(c,1,len(@sp)) = @sp
Return
END