tsql - 在一个表的列中分解csv以插入新表

如何拆分看起来像这样的值:
'some:1234，dumb:4321，thing:6534，someone:65432，did: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 <> ''

对于“事物”中的每条记录，“值”列应拆分两次。第一个拆分提供了新记录的数据。该数据需要再拆分一次才能获得各个字段。

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

编辑

我并没有像我期望的那样清晰，但是你们有一些很棒的想法。我的最终结果应类似于以下内容，插入到新表中。感谢您迄今为止的所有帮助!

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

最佳答案

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

这是你的绳子

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);