sql server array string split challenge-CodePudding

I'm trying to extract auditing information from our ERP system that is stored in an array in a table dbo.SysDatabaseLogStaging. Preference would be to have a view on top of this table outputting in required format. The array information is split into three fields. Field name, Value, Previous Value. Field names are separated by commas and then the two fields are separated by ÿþ (replaced with pipes). Any field names without a value in the first column can be ignored (not returned), also any field names where value is same as previous value (not returned) how to do this in SQL? I've tried using the new string_split function but the functionality is too basic for my requirement...

Example array format

http://sqlfiddle.com/#!18/b4b4da/1

Desired format example

[FieldName]      [Value]               [PreviousValue]
Description      Bloggs
IsPostalAddress  Yes
LocationId       000008307
createdDateTime  7/4/2019 05:33:00 pm
modifiedBy       ads
modifiedDateTime 7/4/2019 05:33:00 pm
Partition        5637144576
RecId            5637158832
recVersion       1

CodePudding user response：

This is really ugly and will only work in Azure SQL Database (or Edge?) presently as ordinal is not supported in any supported on-prem version. You can simulate this with, say, an ordered string splitting function.

 DECLARE @data nvarchar(max) = N'03::Description, | Bloggs |  | IsPostalAddress, | 1 |  | LocationId, | 000008307 |  | ParentLocation, | 0 |  | CreatedDateTime, | Jul  4 2019  4:33PM |  | ModifiedBy, | ads |  | ModifiedDateTime, | Jul  4 2019  4:33PM |  | Partition, | 5637144576 |  | RecId, | 5637158832 |  | RecVersion, | 1 |  | ';

;WITH src AS
(
  SELECT  
    t1ordinal = t1.ordinal, 
    value = t2.value, 
    oval = LAG(t2.value, 1) OVER (ORDER BY t1.ordinal), 
    t2ordinal = t2.ordinal
  FROM STRING_SPLIT(@data, ',', 1) AS t1
  CROSS APPLY STRING_SPLIT(t1.value, '|', 1) AS t2
)
SELECT 
  FieldName = MAX(CASE t2ordinal WHEN 1 THEN oval  END), 
  Value     = MAX(CASE t2ordinal WHEN 2 THEN value END),
  Previous  = MAX(CASE t2ordinal WHEN 3 THEN value END) 
FROM src AS s1
WHERE t1ordinal > 1
GROUP BY t1ordinal;

CodePudding user response：

Using the function DelimitedSplit8K from here you could do something along these lines. This is about 90% of what you need. Just needs a little refinement to clean up the values and maybe not include the last row but you should be able to figure that part out easily enough.

declare @Something varchar(1000) = '03::Description, | Bloggs |  | IsPostalAddress, | 1 |  | LocationId, | 000008307 |  | ParentLocation, | 0 |  | CreatedDateTime, | Jul  4 2019  4:33PM |  | ModifiedBy, | ads |  | ModifiedDateTime, | Jul  4 2019  4:33PM |  | Partition, | 5637144576 |  | RecId, | 5637158832 |  | RecVersion, | 1 |  |   '
;

with GroupValues as
(
    select *
        , GroupNum = ceiling(ROW_NUMBER()over(order by s.ItemNumber) / 3.0)
    from dbo.DelimitedSplit8K(@Something, '|') s
)

select FieldName = max(case when v.ItemNumber % 3 = 1 then Item end)
    , MyValue = max(case when v.ItemNumber % 3 = 2 then Item end)
    , PreviousValue = max(case when v.ItemNumber % 3 = 0 then Item end)
from GroupValues v
group by GroupNum
order by GroupNum

If you want this value to come from your table instead of a variable you would want to use CROSS APPLY. The easiest would be to replace the contents of the GroupValues cte with something like this.

select *
    , GroupNum = ceiling(ROW_NUMBER()over(order by s.ItemNumber) / 3.0)
from YourTable t
cross apply dbo.DelimitedSplit8K(t.YourColumn, '|') s