split comma separated string to integer

Sometime we need to split a comma separated string to a Table of integers. The below function will split the given comma-separated string into integers and process the results.


CREATE Function SplitStringtoInt(@strTemp nVarChar(4000)) Returns @intTable Table([Value] [Int] NOT NULL)
AS
BEGIN
    DECLARE @intValue nVarChar(100)
    DECLARE @pos int
    -- TRIMMING THE BLANK SPACES
    SET @strTemp = LTRIM(RTRIM(@strTemp))+ ',' 
    -- OBTAINING THE STARTING POSITION OF COMMA IN THE GIVEN STRING
    SET @pos = CHARINDEX(',', @strTemp, 1) 
    -- CHECK IF THE STRING EXIST FOR US TO SPLIT
    IF REPLACE(@strTemp, ',', '') <> '' 
    BEGIN
        WHILE @pos > 0
        BEGIN
-- GET THE 1ST INT VALUE TO BE INSERTED
            SET @intValue = LTRIM(RTRIM(LEFT(@strTemp, @pos - 1))) 
            IF @intValue <> ''
            BEGIN
                INSERT INTO @intTable (Value) 
                VALUES (CAST(@intValue AS bigint)) 
            END
            -- RESETTING THE INPUT STRING BY REMOVING THE INSERTED ONES
            SET @strTemp = RIGHT(@strTemp, LEN(@strTemp) - @pos) 
            -- OBTAINING THE STARTING POSITION OF COMMA IN THE RESETTED NEW STRING
            SET @pos = CHARINDEX(',', @strTemp, 1) 
        END
    END    
    RETURN
END

Usage: SELECT * FROM dbo. SplitStringtoInt ('12345,87612,988473')