如何将单个字符串分割为两个不同的列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).

如果我的弦是(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.

我欣赏的帮助。

3 个解决方案

#1

see may following would hepl you

见5月后会有你。

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

#2

Using DelimitedSplit8K

使用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

#3

Another way with XML and recursive CTE:

XML和递归CTE的另一种方式是:

DECLARE @string nvarchar(max) = N'1,5,2,20,3,9', 
        @xml xml 

;WITH cte AS (
    SELECT STUFF(@string,CHARINDEX(',',@string),1,'</a><b>')  s, 1 as LEV
    UNION ALL
    SELECT CASE WHEN LEV % 2 = 0 THEN STUFF(s,CHARINDEX(',',s),1,'</a><b>')
                ELSE STUFF(s,CHARINDEX(',',s),1,'</b></s><s><a>') END, LEV + 1
    FROM cte
    WHERE LEV < (LEN(@string) - LEN(REPLACE(@string,',','')))
)
SELECT TOP 1 @xml = CAST('<s><a>' + s + '</b></s>' as xml)
FROM cte
ORDER BY LEV DESC

SELECT  t.v.value('a[1]', 'int') as size,
        t.v.value('b[1]', 'int') as quantity
FROM @xml.nodes('/s') as t(v)

We get XML like:

我们得到的XML:

<s>
  <a>1</a>
  <b>5</b>
</s>
<s>
  <a>2</a>
  <b>20</b>
</s>
<s>
  <a>3</a>
  <b>9</b>
</s>

Output:

输出:

size    quantity
1       5
2       20
3       9

#1