在一个表的列中拆分csv以插入新表

Question

我如何分割如下的值：‘一些:1234，哑:4321，事物:6534，某人:65432，做:43287’。

这方面：

value1    value2
some      1234
dumb      4321
thing     6534
someone   65432
did       43287

插入到新表中。

下面的代码显示了我试图实现的“外观”。我已经花了大约一天的时间把头发拔出来。

我还包括了表和函数定义以及几行数据，以说明我希望实现的目标。

-- Shamelessly copied this function from the CTE String Split here: https://sqlperformance.com/2012/07/t-sql-queries/split-strings
CREATE FUNCTION dbo.splitStrings -- Just being trying to be complete so adding this function since it is not an mssql built-in
(
   @List       NVARCHAR(MAX),
   @Delimiter  NVARCHAR(255)
)
RETURNS @Items TABLE (Item NVARCHAR(4000))
WITH SCHEMABINDING
AS
BEGIN
   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);

   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, 1), 0), @ll),
           [value] = SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, 1), 0), @ll) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + @ld,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll),
           [value] = SUBSTRING(@List, [end] + @ld, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)
       FROM a
       WHERE [end] < @ll
   )
   INSERT @Items SELECT [value]
   FROM a
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);

   RETURN;
END
GO

-- Primary table where the messed up data currently resides
create table dbo.things
(
    Id int identity(1,1) not null,
    User int not null,
    Values varchar(256)
)
-- This is the bad data
insert into things(USER,Values) values
{100,'some:1234,dumb:4321,thing:6534,someone:65432,did:43287'},
{101,'this:31234,is:43221,so wrong:65482'}

-- This is where the split up data will reside in the future
create table dbo.propertableforvalues
(
        ThingId int not null,
        ValueID int not null,
    ValueName varchar(256) not null
)

-- This doesn't come close to working, but 'looks' like what I'm trying to achieve
insert into dbo.propertableforvalues
select
(
    select 
        t.Id,
        outerSplit.Name,
        outerSplit.ValueId
    from 
        (select -- one 'things' record should create n rows based on the number of items in the comma split of the 'Values' string
            innerT.Name,
                        innerT.ValueId
         from 
                        dbo.splitStrings(
                        (select
                            ValueId,
                            ValueName
                        from
                                dbo.splitStrings(t.Values, ',') -- Split first based on the comma
                                    , ':')) as innerSplit                               -- Split a second time on the colon
        ) as outerSplit
)
from
    things t
where
    t.Values is not null
    and t.Values <> ''

对于“things”中的每条记录，“Value”列应该分割两次。第一个拆分给出了新记录的数据。该数据需要再分割一次才能得到各个字段。

我宁愿远离游标，我有成千上万的记录要更新。

编辑

我并不像我所期望的那么清楚，但是你们有一些很棒的想法。对于插入到新表中，我的最终结果应该如下所示。到目前为止，感谢您的帮助！

ID  Item         Value
1   some         1234
1   dumb         4321
1   thing        6534
1   someone      65432
1   did          43287
2   this         31234
2   is           43221
2   so wrong     65482

Answer 1

在这种情况下，我更喜欢这种方法：

这是你的绳子

DECLARE @csv VARCHAR(100)='some:1234,dumb:4321,thing:6534,someone:65432,did:43287';

一些替换将此字符串转换为XML：

SELECT CAST('<x><y>' + REPLACE(REPLACE((SELECT @csv AS [*] FOR XML PATH('')),',','</y></x><x><y>'),':','</y><y>') + '</y></x>' AS XML);

这就是结果，我们可以使用XML方法进行查询。

<x>
  <y>some</y>
  <y>1234</y>
</x>
<x>
  <y>dumb</y>
  <y>4321</y>
</x>
<x>
  <y>thing</y>
  <y>6534</y>
</x>
<x>
  <y>someone</y>
  <y>65432</y>
</x>
<x>
  <y>did</y>
  <y>43287</y>
</x>

试试看：

DECLARE @csv VARCHAR(100)='some:1234,dumb:4321,thing:6534,someone:65432,did:43287';

SELECT x.value('y[1]','nvarchar(max)') AS Item
      ,x.value('y[2]','nvarchar(max)') AS [Value]
FROM (SELECT CAST('<x><y>' + REPLACE(REPLACE((SELECT @csv AS [*] FOR XML PATH('')),',','</y></x><x><y>'),':','</y><y>') + '</y></x>' AS XML)) t(casted)
CROSS APPLY t.casted.nodes('/x') A(x);

更新

表数据也是如此

DECLARE @things TABLE
(
    Id int identity(1,1) not null,
    [User] int not null,
    [Values] varchar(256)
)
insert into @things([USER],[Values]) values
(100,'some:1234,dumb:4321,thing:6534,someone:65432,did:43287'),
(101,'this:31234,is:43221,so wrong:65482');

SELECT t.Id
      ,t.[User] 
      ,x.value('y[1]','nvarchar(max)') AS Item
      ,x.value('y[2]','nvarchar(max)') AS [Value]
FROM @things t
CROSS APPLY (SELECT CAST('<x><y>' + REPLACE(REPLACE((SELECT t.[Values] AS [*] FOR XML PATH('')),',','</y></x><x><y>'),':','</y><y>') + '</y></x>' AS XML)) A(casted)
CROSS APPLY A.casted.nodes('/x') B(x);

Answer 2

一个选项是将字符串转换为XML，然后简单地解析XML。

示例

Declare @S varchar(max) = 'some:1234,dumb:4321,thing:6534,someone:65432,did:43287'


Declare @XML xml = '<row '+replace(replace(@S,',','" '),':','="')+'"/>'

Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
      ,Value = xAttr.value('.','varchar(max)')
 From  @XML.nodes('//@*') xNode(xAttr)

返回

Item    Value
some    1234
dumb    4321
thing   6534
someone 65432
did     43287

编辑-通知数据在表中

Declare @YourTable table (ID int,SomeCol varchar(max))
Insert Into @YourTable values
 (1,'some:1234,dumb:4321,thing:6534,someone:65432,did:43287')
,(2,'aaa:111,bbb:222,ccc:333,ddd:444,eee:555')


Select A.ID
      ,C.*
 From  @YourTable A
 Cross Apply ( values ( try_convert(xml,'<row '+replace(replace(SomeCol,',','" '),':','="')+'"/>'))
             ) B(xmlData) 
 Cross Apply (
                Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Value = xAttr.value('.','varchar(max)')
                 From  XMLData.nodes('//@*') xNode(xAttr)
             ) C

返回

ID  Item    Value
1   some    1234
1   dumb    4321
1   thing   6534
1   someone 65432
1   did     43287
2   aaa     111
2   bbb     222
2   ccc     333
2   ddd     444
2   eee     555

Answer 3

作为选项之一，我将发布非xml方法来完成相同的任务，而不是破坏先前给出的答案。

为了使此方法工作，@CSV字符串必须在开头和结尾用分隔符括起来：

DECLARE @csv VARCHAR(8000) = 'some:1234,dumb:4321,thing:6534,some one:65432,did:43287'

SET @csv = ',' + @csv + ','

首先，构建一个数字cte (也称为Tally)：

-- Build 10000 numbers.
;WITH
TENS (N)      AS (SELECT 0 UNION ALL SELECT 0 UNION ALL
                  SELECT 0 UNION ALL SELECT 0 UNION ALL
                  SELECT 0 UNION ALL SELECT 0 UNION ALL
                  SELECT 0 UNION ALL SELECT 0 UNION ALL
                  SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1
                FROM TENS t1
                     CROSS JOIN TENS t2
                     CROSS JOIN TENS t3),
TALLY (N)     AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))
                FROM THOUSANDS)

并查找分隔符(分隔符)的索引，在本例中为“，”。

SELECT N
FROM TALLY
WHERE SUBSTRING(@csv,N,1) = ','

然后使用SUBSTRING，用分隔符的索引值对CSV切片，得到第一部分，然后用新的分隔符':'重复相同的事情

DECLARE @csv VARCHAR(8000) = 'some:1234,dumb:4321,thing:6534,some one:65432,did:43287'

SET @csv = ',' + @csv + ','

-- Build 10000 numbers.
;WITH
TENS (N)      AS (SELECT 0 UNION ALL SELECT 0 UNION ALL
                  SELECT 0 UNION ALL SELECT 0 UNION ALL
                  SELECT 0 UNION ALL SELECT 0 UNION ALL
                  SELECT 0 UNION ALL SELECT 0 UNION ALL
                  SELECT 0 UNION ALL SELECT 0),
THOUSANDS (N) AS (SELECT 1
                FROM TENS t1
                     CROSS JOIN TENS t2
                     CROSS JOIN TENS t3),
TALLY (N)     AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))
                FROM THOUSANDS)
--Split CSV values into columns
,SPLITTED AS (
            SELECT SUBSTRING(@csv,N+1,CHARINDEX(',',@csv,N+1)-N-1) AS ColName
            FROM TALLY
            WHERE N < LEN(@csv) AND SUBSTRING(@csv,N,1) = ','  )

--Split column values further into as multiple columns
SELECT Part1 = SUBSTRING(ColName,1,CHARINDEX(':', ColName,1)-1),
       Part2 = SUBSTRING(ColName,CHARINDEX(':', ColName,1)+1,LEN(ColName))
FROM SPLITTED