-- =============================================
-- Author: 代码乱了(靳如坦)
-- Create date: 2008-06-10
-- Description: 把以','分隔的字符串,转换成table
-- 示例 :select * from [dbo].[SpliteStringToList]('123,xxx,dfdsf,rer')
-- =============================================
create Function [dbo].[SpliteStringToList](@strings varchar(2000))
Returns @ReturnTable Table(ID varchar(max))
As
Begin
Insert @ReturnTable
select substring(c,2,charindex(',',c,2)-2) as empno from
(
select substring(csv.emps,iter.pos,len(csv.emps)) as c from
(
select ','+@strings+',' 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) > 1 and substring(c,1,1) = ','
Return
End