Pulling floats to sum data in array structure using SQL-CodePudding

I'm trying to pull numbers from an array structure and then I want to sum them.

Example row entry:

{"DBA":50.0},{"RST":132.0},{"ZIT":752}

I would want to sum all of the number values so 50 132 752 = 934

What I have tried: col = column name

SELECT SUBSTRING(col, LEN(LEFT(col, CHARINDEX (':', col)))   1, LEN(col) - LEN(LEFT(col, 
    CHARINDEX (':', col))) - LEN(RIGHT(col, LEN(col) - CHARINDEX ('}', Benefit))) - 1)    
FROM table

This works to grab the first value (so 50.0) in the above example, but will not grab each value. Any idea how I can make this query grab multiple values and then sum them together?

CodePudding user response：

The content is almost a valid JSON, so you may try to fix it and parse it with built-in JSON support using OPENJSON() (a valid JSON content is [{"DBA":50.0},{"RST":132.0},{"ZIT":752}]):

SELECT 
  t.[Column],
  [Sum] = (
     SELECT SUM(CONVERT(numeric(10, 1), j2.value)) 
     FROM OPENJSON(CONCAT('[', t.[Column], ']')) j1
     CROSS APPLY OPENJSON(j1.[value]) j2
  )
FROM (VALUES
   ('{"DBA":50.0},{"RST":132.0},{"ZIT":752}')
) t ([Column])

CodePudding user response：

I would, personally, convert your data into actual well formed JSON. Then you can easily SUM the values:

DECLARE @YourString nvarchar(MAX) = N'{"DBA":50.0},{"RST":132.0},{"ZIT":752}';

SELECT SUM(TRY_CONVERT(decimal(5,1),[value]))
FROM (VALUES(CONCAT('{',REPLACE(REPLACE(@YourString,'{',''),'}',''),'}')))V(JSONString)
     CROSS APPLY OPENJSON(V.JSONString);

Or you could add a WITH to the OPENJSON call and then add ( ) the values:

DECLARE @YourString nvarchar(MAX) = N'{"DBA":50.0},{"RST":132.0},{"ZIT":752}';

SELECT OJ.DBA   OJ.RST   OJ.ZIT
FROM (VALUES(CONCAT('{',REPLACE(REPLACE(@YourString,'{',''),'}',''),'}')))V(JSONString)
     CROSS APPLY OPENJSON(V.JSONString)
                 WITH (DBA decimal(5,1),
                       RST decimal(5,1),
                       ZIT decimal(5,1)) OJ;