How can I split a single string into two different columns sql

Question

I have two columns and I want to create a splitting function that will accept a single string and put every two consecutive numbers into my columns.

for example:

If my string was (1,5,2,20,3,9).

The result should be:

size   quantity
 1   |  5
 2   |  20
 3   |  9

Here is the code that I have been trying with it:

create FUNCTION [dbo].[getSizesAndQuantity1](@input AS Varchar(4000) )
RETURNS
  @Result TABLE(Size BIGINT, Quantity BIGINT)
AS
BEGIN
  DECLARE @str int
  DECLARE @strQ int
  DECLARE @ind Int
  IF(@input is not null)
  BEGIN
        SET @ind = CharIndex(',',@input)
        WHILE @ind > 0
        BEGIN
              SET @str = SUBSTRING(@input,1,@ind-1)
              SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)
              SET @strQ = SUBSTRING(@input,1,@ind-1)
              SET @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)
              INSERT INTO @Result(Size,Quantity) values (@str,@strQ)
              SET @ind = CharIndex(',',@input)
        END


  END
  RETURN
END

I appreciate the help.

Answer 1

Using DelimitedSplit8K

select  size = max(case when ItemNumber % 2 = 1 then Item end),
    quantity = max(case when ItemNumber % 2 = 0 then Item end)
from    DelimitedSplit8K('1,5,2,20,3,9', ',') 
group by (ItemNumber - 1) / 2