时间戳

首页 > 解决方案 > 在一个表的列中分解 csv 以插入新表

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

标签： tsqlsql-server-2012

解决方案

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

这是你的字符串

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

推荐阅读