Ms Sql Split and Convert

Question

I have ID as a varchar. I need to split as Comma after that i need to convert list to int32. I added example like below.

DECLARE @List varchar(255)

SET @List = '1, 2, 3, 4, 5, 6'

print @List

Answer 1

You can try something like:

DECLARE @List varchar(255) = '1, 2, 23, 4, 5, 6'

DECLARE @X XML
SELECT @X  = '<myxml><nodes><n>' + 
              REPLACE(@List,',','</n></nodes><nodes><n>') + 
             '</n></nodes></myxml>'

--SELECT @X

SELECT LTRIM(C.value('n[1]','VARCHAR(50)')) AS item1
FROM @X.nodes('/myxml/nodes') Cols (C)

Fiddle demo

Results:

| ITEM1 |
--------|--
|     1 |
|     2 |
|    23 |
|     4 |
|     5 |
|     6 |

Note: if you have spaces between numbers as per your sample data (2 3), you can use Replace() function to remove them before converting to an INT. IsNumeric() function may also be useful (Please read the note section of isnumeric() function for more details).

Answer 2

I have adapted a script from p.s.w.g's answer which split a string into a in memory table of int :

CREATE FUNCTION [dbo].[SplitToIntList] 
    ( @string nvarchar(4000)
    , @delim nvarchar(100) )
RETURNS
    @result TABLE ([Value] int NULL)
AS
BEGIN
    DECLARE @str nvarchar(4000)
          , @pos int 
          , @prv int = 1

    SELECT @pos = CHARINDEX(@delim, @string)
    WHILE @pos > 0
    BEGIN
        SELECT @str = SUBSTRING(@string, @prv, @pos - @prv)
        INSERT INTO @result SELECT CAST(@str AS INT)

        SELECT @prv = @pos + LEN(@delim)
             , @pos = CHARINDEX(@delim, @string, @pos + 1)
    END

    INSERT INTO @result SELECT CAST(SUBSTRING(@string, @prv, 4000) AS INT)
    RETURN
END

HTH