A better way to parse integer values from a T-SQL delimited string

I have a SQLServer2008 R2 Stored Procedure that contains an algorithm for parsing out integers from a delimited string.

Here's an example of the SQL code that I made for looping through the delimited string and extracting any numbers that may exist in the delimited string:

-- Create a delimited list for testing
DECLARE @NumericList nvarchar(MAX) = N'1, 33,44 ,55, foo ,666,77 77,8,bar,9,10'

-- Declare the delimiter
DECLARE @ListDelimiter VARCHAR(1) = ','

-- Remove white space from the list
SET @NumericList = REPLACE(@NumericList, ' ','');

-- Var that will hold the value of the delimited item during the while-loop
DECLARE @NumberInScope VARCHAR(MAX)

WHILE(LEN(@NumericList) > 0)
BEGIN
    -- Get the value to the left of the first delimiter.
    IF(CHARINDEX(@ListDelimiter, @NumericList) > 0)
        SET @NumberInScope = LEFT(@NumericList, CHARINDEX(@ListDelimiter, @NumericList))
    ELSE
        SET @NumberInScope = @NumericList   

    -- Remove the @NumberInScope value fr开发者_Python百科om the @NumericList
    SET @NumericList = RIGHT(@NumericList, LEN(@NumericList) - LEN(@NumberInScope))

    -- Remove the delimiter from the @NumberInScope
    SET @NumberInScope = REPLACE(@NumberInScope,@ListDelimiter,'')

    -- Print only the integer values
    IF(ISNUMERIC(@NumberInScope) = 1)
    BEGIN
        PRINT @NumberInScope
    END 
END

The code above works fine, but after reviewing the code it seems to me that there's got to be a more concise way of doing the same thing. In other words, is there any string functions (or any new R2 function, maybe) that I'm overlooking that I can implement that would shrink the code and, hopefully, be easier to read?

The definitive article is "Arrays and Lists in SQL Server 2005 and Beyond"

Here several methods are shown for splitting CSVs: CLR, Numbers table, WHILE loops

Here is the code, you can also create a 'split' function and use that

DECLARE @NumericList nvarchar(max) = N'1, 33,44 ,55, foo ,666,77 77,8,bar,9,10'

;WITH cte as (
SELECT CAST(1 as bigint) p1,  CHARINDEX(',', @NumericList+',') p2, 
CAST(null as Nvarchar(max)) NumberInScope 
UNION ALL
SELECT p2 + 1, CHARINDEX(',',@NumericList+',', p2 + 1), 
SUBSTRING(@NumericList, p1, p2-p1) 
FROM cte WHERE p2>0
)
SELECT NumberInScope from cte WHERE isnumeric(NumberInScope) > 0
OPTION (MAXRECURSION 0)

From MSSql Server 2016, there is a new keyword introduced STRING_SPLIT to do the desired operation.

SELECT STRING_SPLIT ( string , separator )

Refer https://msdn.microsoft.com/en-us/library/mt684588.aspx