SQL触发器，用于在insert期间拆分字符串，而不使用公共分隔符，并将其存储到另一个表中

Question

目前我有一个系统，它将数据转储到一个表中，格式如下：

Table1
Id#, row#, row_dump
222, 1, “set1  = aaaa     set2 =aaaaaa  aaaa  dd set4=1111”

我想要获取行转储，并将其转置为行，然后将其插入到另一个格式为：

Table2
Id#, setting, value
222, ‘set1’,’aaa’ 
222, ‘set2’,’aaaaaa  aaaa  dd’
222, ‘set4’,’1111’

有没有办法在MSSQL中创建一个触发器，在Table1中插入时解析这个字符串，并正确地将它插入到Table2中？

我发现的所有示例都需要一个公共分隔符。‘=’将设置与值分开，用空格将值与设置分开，但值中可以有空格(设置中没有空格，因此等号前的最后一个单词是设置名称，但设置名称和等号之间可以有空格)。

在任何给定行中可能有1-5个设置和值。值可以包含空格。设置名称和‘=’符号之间可能有空格，也可能没有空格。

我无法控制原始的插入过程或格式，因为它被用于其他目的。

Answer 1

您可以使用'set‘作为分隔符。这是一个简单的示例。显然，它可能必须根据您的环境进行塑造。

use tempdb
GO

IF OBJECT_ID('dbo.fn_TVF_Split') IS NOT NULL
DROP FUNCTION dbo.fn_TVF_Split;
GO

CREATE FUNCTION dbo.fn_TVF_Split(@arr AS NVARCHAR(2000), @sep AS NCHAR(3))
RETURNS TABLE
AS
RETURN
WITH 
   L0 AS (SELECT 1 AS C UNION ALL SELECT 1)       --2 rows
  ,L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B)    --4 rows (2x2)
  ,L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B)    --16 rows (4x4)
  ,L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B)    --256 rows (16x16)
  ,L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B)    --65536 rows (256x256)
  ,L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B)    --4,294,967,296 rows (65536x65536)
  ,Nums AS (SELECT row_number() OVER (ORDER BY (SELECT 0)) AS N FROM L5)  
SELECT
(n - 1) - LEN(REPLACE(LEFT(@arr, n-1), @sep, N'')) + 1 AS pos,
SUBSTRING(@arr, n, CHARINDEX(@sep, @arr + @sep, n) - n) AS element
FROM Nums
WHERE 
    n <= LEN(@arr) + 3
    AND SUBSTRING(@sep + @arr, n, 3) = @sep
    AND N<=100000
GO

declare @t table(
Id int,
row int,
row_dump varchar(Max)
);

insert into @t values(222, 1, 'set1 = aaaa set2 =aaaaaa aaaa dd set4=1111')
insert into @t values(111, 2, ' set1 =cx set2 =4444set4=124')

DECLARE @t2 TABLE(
Id int,
Setting VARCHAR(6),
[Value] VARCHAR(50)
)

insert into @t2 (Id,Setting,Value)
select 
    Id,
    [Setting]='set' + left(LTRIM(element),1),
    [Value]=RIGHT(element,charindex('=',reverse(element))-1)
from @t t
cross apply dbo.fn_TVF_Split(row_dump,'set')
where pos > 1
order by 
    id asc, 
    'set' + left(LTRIM(element),1) asc

select *
from @t2

更新

你可以做这样的事情。它不是最优的，也许可以在转换工具或应用程序中得到更好的处理。不管怎样，我们开始吧。

注意:您将需要我之前发布的拆分函数。

declare @t table(
Id int,
row int,
row_dump varchar(Max)
);

insert into @t values(222, 1, 'set1 = aaaa set2 =aaaaaa aaaa dd set3=abc set4=1111 set5=7373')
insert into @t values(111, 2, 'set1 =cx set2 = 4444 set4=124')

DECLARE @t2 TABLE(
Id int,
Setting VARCHAR(6),
[Value] VARCHAR(50)
)

if OBJECT_ID('tempdb.dbo.#Vals') IS NOT NULL
BEGIN 
    DROP TABLE #Vals;
END

CREATE TABLE #Vals(
Id INT,
Row INT,
Element VARCHAR(MAX),
pos int,
value VARCHAR(MAX)
);

insert into #Vals
select 
    Id,
    row,
    element,
    pos,
    Value=STUFF(LEFT(element,len(element) - CHARINDEX(' ',reverse(element))),1,1,'')
from(
    select 
        Id,
        row,
        row_dump = REPLACE(REPLACE(REPLACE(row_dump,'= ','='),' =','='),'=','=|')
    from @t
) AS t
cross apply dbo.fn_TVF_Split(row_dump,'=')
where pos >=1 and pos < 10


insert into @t2 (Id,Setting,Value)
select 
    t1.Id,
    Setting =
    (
        SELECT TOP 1 
            CASE WHEN t2.pos = 1 
                 THEN LTRIM(RTRIM(t2.element))
            ELSE LTRIM(RTRIM(RIGHT(t2.element,CHARINDEX(' ',REVERSE(t2.element)))))
            END
        FROM #Vals t2 
        where 
            t2.Id = t1.id 
            and t2.row = t1.row 
            and t2.pos < t1.pos 
        ORDER BY t2.pos DESC
    ),
    t1.Value
from #Vals t1
where t1.pos > 1 and t1.pos < 10
order by t1.id,t1.row,t1.pos

select * from @t2