sql - 使用 SQL Server 将字符串数据块解析为表格数据集

Question

我从 .NET Web 应用程序的用户界面获取大量字符串数据。.NET 开发人员想要调用需要将字符串数据解析为表格格式的用户定义存储过程。

示例字符串数据如下：

[EMPDetailsEntity(id=1, FName=ABC, Active=YES, LName=CCC, Created=Mon Jun 07 20:00:00 EDT 2021, UpdatedBy = null)
,EMPDetailsEntity(id=2, FName=DEF, Active=YES, LName=F'FF, Created=Sun Jun 13 20:00:00 EDT 2021, UpdatedBy = null)
,EMPDetailsEntity(id=3, FName=GH,I, Active=YES, LName=III, Created=Wed Jun 16 20:20:20 EDT 2021, UpdatedBy = null)
,EMPDetailsEntity(id=4, FName=JKL, Active=YES, LName=LL',L, Created=Wed Jun 16 20:20:20 EDT 2021, UpdatedBy = null)]

我想将上述字符串数据生成表格格式。如下所示：

ID  FirstName  LastName  Active  CreatedDT   UpdatedBy
1     ABC        CCC       YES     06/07/2021  Null
2     DEF        FFF       YES     06/13/2021  Null
3     GHI        III       YES     06/16/2021  Null
4     JKL        LLL       YES     06/16/2021  Null

请任何人都可以帮助我如何将字符串数据解析为表格格式，以便我可以直接将数据推送到 SQL 表中。

标签： sqlsql-servertsqlsql-server-2012

Answer 1

在表值函数与条件聚合的帮助下

示例或dbFiddle

Declare @S varchar(max) = '
[EMPDetailsEntity(id=1, FName=ABC, Active=YES, LName=CCC, Created=Mon Jun 07 20:00:00 EDT 2021, UpdatedBy = null)
,EMPDetailsEntity(id=2, FName=DEF, Active=YES, LName=F''FF, Created=Sun Jun 13 20:00:00 EDT 2021, UpdatedBy = null)
,EMPDetailsEntity(id=3, FName=GH,I, Active=YES, LName=III, Created=Wed Jun 16 20:20:20 EDT 2021, UpdatedBy = null)
,EMPDetailsEntity(id=4, FName=JKL, Active=YES, LName=LL'',L, Created=Wed Jun 16 20:20:20 EDT 2021, UpdatedBy = null)]
'

Select ID        = max(case when Item='id'        then value end)
      ,FirstName = max(case when Item='FName'     then value end)
      ,LastName  = max(case when Item='LName'     then value end)
      ,Active    = max(case when Item='Active'    then value end)
      ,CreateDt  = try_convert(date,stuff(stuff(max(case when Item='Created' then value end),11,13,''),1,4,''))
      ,UpdatedBy = case when max(case when Item='UpdatedBy' then value end) like '%null%' then null else max(case when Item='UpdatedBy' then value end) end
 From (
        Select Grp   = A.RetSeq
              ,Item  = left(B.RetVal,charindex('=',B.RetVal+'=')-1)
              ,Value = stuff(B.RetVal,1,charindex('=',B.RetVal+'='),'')
         From  [dbo].[tvf-Str-Parse](replace(replace(replace(replace(@S,'EMPDetailsEntity',''),')',''),']',''),'[',''),'(')  A
         Cross Apply [dbo].[tvf-Str-Parse](A.RetVal,', ') B
         Where len(A.RetVal)>25
      ) src
 Group By Grp

 

结果

感兴趣的功能

CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = row_number() over (order by 1/0)
          ,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  ( values (cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.'))) as A(x)
    Cross Apply x.nodes('x') AS B(i)
);
--Usage: Select * from [dbo].[tvf-Str-Parse]('Dog,Cat,House,Car',',')