Interesting idea indeed! However there are obviously some limitations (maxrecursion being one of those), plus the performance 'may' not be up to the mark.

The best method to split a integer CSV to a string (w.r.t. performance) is the 'crude' one i.e. finding and extracting integers one by one (see below sample user defined function). We have compared various approaches and found this method to be most performing of all the implementations.

Create Function dbo.udf_1 (

@csv varchar(max)

)

Returns @tbl Table (IntValue int)

As

Begin

If( @csv Is Null Or Len(@csv) = 0 )

Return

Declare @iStartPosition int, @iEndPosition int,

@vcTmpId varchar(15), @cDelimiter char(1)

Select @iStartPosition = 1, @cDelimiter = ',', @iEndPosition = charindex( @cDelimiter, @csv )

While @iEndPosition <> 0

Begin

Select @vcTmpId = substring(@csv, @iStartPosition, @iEndPosition - @iStartPosition)

Select @iStartPosition = @iEndPosition + 1

Insert Into @tbl Values( @vcTmpId )

Select @iEndPosition = charindex( @cDelimiter, @csv, @iStartPosition )

End

Select @vcTmpId = substring(@csv, @iStartPosition, Len(@csv) - @iStartPosition + 1)

Insert Into @tbl Values( @vcTmpId )

Return

End

Go

-- Usage

Select * From dbo.udf_1('1,2,2342,3534,46546,4354,22')

Go