How to split string value into Integer

How to split string value into Integer

--==================================================================

-- how to split string value in to int using delimmiter (you can do these by creating TVF-function.

--==================================================================

CREATE FUNCTION [dbo].[SplitStringReturnInt]

(      @string varchar(max)

       ,@delimiter varchar(10)

)

RETURNS

@return TABLE (id int)

AS

BEGIN

       SET @string = RTRIM(LTRIM(@delimiter)) + RTRIM(LTRIM(@string)) + RTRIM(LTRIM(@delimiter))

       DECLARE @x bit = 1

      

       ;WITH E1(N)

       AS

       (      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

              UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

       ), E2(N)

       AS

       (      SELECT 1

              FROM E1 a, E1 b

       ), E4(N)

       AS

       (      SELECT ROW_NUMBER() OVER(ORDER BY @x)

              FROM E2 a, E2 b, E2 c

       )

       INSERT INTO @return

    SELECT    CASE ISNUMERIC(SUBSTRING(@string, N + 1, CHARINDEX(@delimiter, @string, N + 1) - N -1))

                           WHEN 1 THEN SUBSTRING(@string, N + 1, CHARINDEX(@delimiter, @string, N + 1) - N -1)

                     END

    FROM E4

    WHERE N <= LEN(@string) - 1

              AND SUBSTRING(@string, N, 1) = @delimiter

      

       RETURN

END

-- Now Lets Test these function.

Declare @stre varchar(max) = ('1234567891,234234,23424,2342,324234,2,4234,23423,243634,345435,53453')

SELECT * FROM [dbo].[SplitStringReturnInt](@stre,',')

Declare @stre varchar(max) = ('5345.53453.3453534.345345.3453453.435345.3')

SELECT * FROM [dbo].[SplitStringReturnInt](@stre,'.')