create function dbo.ufn_SplitIntArray( @List varchar(max) ,@Delimiter varchar(10)
)returns table
as
return
select Item=convert(int,String)from dbo.Numbers with (nolock)
cross apply (select ItemPos=Number) F1cross apply (select DelimPos=charindex(@Delimiter,@List+@Delimiter,ItemPos)) F2cross apply (select String=rtrim(ltrim(substring(@List,ItemPos,DelimPos-ItemPos)))) F3where ItemPos<=convert(int,len(@List)) and substring(@Delimiter+@List,ItemPos,1)=@Delimiter
You can see how this function is used in the example below:
select * from dbo.ufn_SplitIntArray('123,456,789',',')/*
Item
----
123
456
789
*/
from: http://bradsruminations.blogspot.com/2010/08/taking-hint.html
