Nysa Nysa - 5 months ago 13
SQL Question

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

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

see may following would hepl you

DECLARE @Result TABLE
(
  Size BIGINT ,
  Quantity BIGINT
)

DECLARE @str INT ,
@input VARCHAR(MAX) = '1,20,2,10,3,15,5,20'
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)) 
            SET @ind = CHARINDEX(',', @input)
            IF ( @ind > 0 )
                BEGIN
                    SET @strQ = SUBSTRING(@input, 1, @ind - 1)
                    SET @input = SUBSTRING(@input, @ind + 1, LEN(@input)) 
                END
            ELSE
                BEGIN
                    SET @strQ = @input
                END

            INSERT  INTO @Result
                    ( Size, Quantity )
            VALUES  ( @str, @strQ )
            SET @ind = CHARINDEX(',', @input) 
            SET @str = NULL
            SET @strQ = NULL
        END
END

SELECT    *
FROM      @Result
Comments