SQL split-string as (key-identity,value)

问题:

I've added a function to my DB that splits a comma separated string into separate rows.

Now in my string I have: 1,55,2,56,3,57,etc... where (1) is the rowID and (55) the value I want to enter into row 1 of my table.

How can I modify this function to pull the 1st,3rd,5th,etc... values and 2nd,4th,6th,etc... values into two different columns?

CREATE FUNCTION dbo.SplitStringToValues
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  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 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
  SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
    FROM cteStart s;
go

-------------- Update

Thanks everyone for your examples. I'm going to try out each of these until I get something working. I will accept once i figure out which on I can make work.

Thank you, Alexp

回答1:

An attempt to help with batch script; please try it out:

DECLARE @List NVARCHAR(MAX) = '1,55,2,56,3,57,10,65,11,88';
DECLARE @Delimiter NVARCHAR(255) = ',';

DECLARE @ListDataTable TABLE
(
  ID INT IDENTITY (1, 1)
  ,DataKey INT
  ,DataValue INT
)

INSERT INTO @ListDataTable  (DataKey, DataValue) 
SELECT 
  value
  ,LEAD(value, 1, 0) OVER(ORDER BY (SELECT 1))
FROM STRING_SPLIT(@List, @Delimiter) WHERE RTRIM(value) <> '';

-- To get odd key values
SELECT * FROM 
(
  SELECT DataKey, DataValue FROM @ListDataTable WHERE ID % 2 = 1
) Temp WHERE DataKey % 2 = 1;

-- To get even key values
SELECT * FROM 
(
  SELECT DataKey, DataValue FROM @ListDataTable WHERE ID % 2 = 1
) Temp WHERE DataKey % 2 = 0;

回答2:

Modify your function to return two columns: the position and the value. This is easy enough and keeps the function general purpose. Just change the select to:

SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter, @List, s.N1), 0) - s.N1, 8000)),
       ItemNum = row_number() over (order by s.N1)
FROM cteStart s;

Then you can use to get the information you want. Here is one method:

  select max(case when ItemNum % 2 = 1 then Item end) as rownum,
         max(case when ItemNum % 2 = 0 then Item end) as value
  from dbo.SplitStringToValues('1,55,2,56,3,57', ',')
  group by (ItemNum - 1) / 2

回答3:

@Macwise was on to something with LEAD - you could do this:

SELECT rownum = item, value
FROM 
(
  SELECT itemnumber, item, value = LEAD(item,1) OVER (ORDER BY itemnumber)
  FROM dbo.SplitStringToValues('1,44,2,55,3,456,4,123,5,0', ',')
) split
WHERE 1 = itemnumber%2;

Gordon's solution is the best, most elegant pre-2012 solution. Here's another pre-2012 solution that does not require a sort in the execution plan:

SELECT rownum = s1.Item, value = s2.Item
FROM DelimitedSplit8K(@string, ',') s1
INNER MERGE JOIN SplitStringToValues('1,44,2,55,3,456,4,123,5,0', ',') s2 
  ON 1 = s1.itemNumber % 2 AND s1.ItemNumber = s2.ItemNumber-1;

回答4:

Instead of changing that function, to get the next row's value next to the id use the LEAD function introduced in SQL SERVER 2012:

SELECT Id, Value
FROM   (SELECT 
               ROW_NUMBER() over (order by(select 1)) as cnt,
               t.item AS Id, 
               Lead(t.item) 
                 OVER ( 
                   ORDER BY (SELECT 1)) Value 
        FROM   dbo.Splitstringtovalues('10,20,30,40,50,10,20,30,40,50,60,70', ',') 
               t) 
       keyValue 
WHERE  keyValue.value IS NOT NULL 
and cnt % 2 = 1