How to split the values from a string field in SQL

Question

I have string field like below:

declare @rawData nvarchar (max) =

'{"request_id":"801707","final_decision":"PASS","derived_Attribute_3":"PASS|Number of Total Active Institutions :3","derived_Attribute_1":"PASS|Number of active MFI :0","derived_Attribute_2":"PASS|Overdue Amount:0.0","derived_Attribute_4":"PASS|Total Exposure + Applied Amount :76384.0","derived_Attribute_5":"PASS|Write off amount:0.0","cbResponseMsg":"Final Decision:PASS || Number of Total Active Institutions :3 || Number of active MFI :0 || Overdue Amount:0.0 || Total Exposure + Applied Amount :76384.0 || Write off amount:0.0"}'

here I need to manipulate the string to get some specific values i.e. get the string after the word derived_Attribute, but not to show the words 'PASS|' or 'FAIL|' and show them in a separate field.

Expected output:

derived_Attribute_1   derived_Attribute_2   derived_Attribute_3  derived_Attribute_4  derived_Attribute_5
 0                      0.0                   3                    76384.0             0.0

Note - I'm using MSSQL 2014 or lesser version

Answer 1

Please try below solution, up vote if found useful

Split function:

CREATE FUNCTION [dbo].[SplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Final Query

DECLARE @rawData NVARCHAR (max) = 
    '{"request_id":"801707","final_decision":"PASS","derived_Attribute_3":"PASS|Number of Total Active Institutions :3","derived_Attribute_1":"PASS|Number of active MFI :0","derived_Attribute_2":"PASS|Overdue Amount:0.0","derived_Attribute_4":"PASS|Total Exposure + Applied Amount :76384.0","derived_Attribute_5":"PASS|Write off amount:0.0","cbResponseMsg":"Final Decision:PASS || Number of Total Active Institutions :3 || Number of active MFI :0 || Overdue Amount:0.0 || Total Exposure + Applied Amount :76384.0 || Write off amount:0.0"}'

CREATE TABLE #table 
  ( 
     id      INT IDENTITY(1, 1), 
     value   VARCHAR(200), 
     colname VARCHAR(100) 
  ) 

CREATE TABLE #result 
  ( 
     colname  VARCHAR(100), 
     colvalue VARCHAR(50), 
  ) 

INSERT INTO #table 
            (value, 
             colname) 
SELECT splitdata, 
       Substring(splitdata, 2, Charindex('":"', splitdata) - 1) 
FROM   Splitstring(@rawData, ',') 
WHERE  splitdata LIKE '%derived_Attribute%' 
ORDER  BY splitdata 

--, SUBSTRING(splitdata,2,charindex('":"',splitdata)-1)  
DECLARE @count INT=1 
DECLARE @recordcount INT=0 

SELECT @recordcount = Count(*) 
FROM   #table 

WHILE( @count < @recordcount ) 
  BEGIN 
      DECLARE @strValue VARCHAR(200), 
              @strName  VARCHAR(200) 

      SELECT @strValue = value, 
             @strName = colname 
      FROM   #table 
      WHERE  id = @count 

      INSERT INTO #result 
      SELECT Replace(@strName, '"', ''), 
             Replace(splitdata, '"', '') 
      FROM   Splitstring(@strValue, ':') 
      WHERE  Isnumeric(Replace(splitdata, '"', '')) = 1 

      SET @count=@count + 1 
  END 

SELECT * 
FROM   #result 

SELECT * 
FROM   (SELECT colname, 
               colvalue 
        FROM   #result) DS 
       PIVOT ( Max(colvalue) 
             FOR [colname] IN ([derived_Attribute_1], 
                               [derived_Attribute_2], 
                               [derived_Attribute_3], 
                               [derived_Attribute_4]) ) PVT; 

DROP TABLE #table 

DROP TABLE #result 

Answer 2

I'll place this as a second answer, as it is a completely different appraoch:

You can use a double-split-in-one-go like here:

declare @rawData nvarchar (max) =
'{"request_id":"801707","final_decision":"PASS","derived_Attribute_3":"PASS|Number of Total Active Institutions :3","derived_Attribute_1":"PASS|Number of active MFI :0","derived_Attribute_2":"PASS|Overdue Amount:0.0","derived_Attribute_4":"PASS|Total Exposure + Applied Amount :76384.0","derived_Attribute_5":"PASS|Write off amount:0.0","cbResponseMsg":"Final Decision:PASS || Number of Total Active Institutions :3 || Number of active MFI :0 || Overdue Amount:0.0 || Total Exposure + Applied Amount :76384.0 || Write off amount:0.0"}';

SELECT CastedToXml 
FROM (VALUES(CAST(CONCAT('<x><y>',REPLACE(REPLACE(REPLACE(REPLACE(@rawData,'{"',''),'"}',''),'":"','</y><y>'),'","','</y></x><x><y>'),'</y></x>') AS XML)))A(CastedToXml)

This will return an XML like this:

<x>
  <y>request_id</y>
  <y>801707</y>
</x>
<x>
  <y>final_decision</y>
  <y>PASS</y>
</x>
<x>
  <y>derived_Attribute_3</y>
  <y>PASS|Number of Total Active Institutions :3</y>
</x>
<x>
  <y>derived_Attribute_1</y>
  <y>PASS|Number of active MFI :0</y>
</x>
<x>
  <y>derived_Attribute_2</y>
  <y>PASS|Overdue Amount:0.0</y>
</x>
<x>
  <y>derived_Attribute_4</y>
  <y>PASS|Total Exposure + Applied Amount :76384.0</y>
</x>
<x>
  <y>derived_Attribute_5</y>
  <y>PASS|Write off amount:0.0</y>
</x>
<x>
  <y>cbResponseMsg</y>
  <y>Final Decision:PASS || Number of Total Active Institutions :3 || Number of active MFI :0 || Overdue Amount:0.0 || Total Exposure + Applied Amount :76384.0 || Write off amount:0.0</y>
</x>

With a query like this you can read the attribute's values:

SELECT CastedToXml.value('(/x[y[1]/text()="derived_Attribute_1"]/y[2]/text())[1]','nvarchar(max)') AS  derived_Attribute_1
      ,CastedToXml.value('(/x[y[1]/text()="derived_Attribute_2"]/y[2]/text())[1]','nvarchar(max)') AS  derived_Attribute_2
      ,CastedToXml.value('(/x[y[1]/text()="derived_Attribute_3"]/y[2]/text())[1]','nvarchar(max)') AS  derived_Attribute_3
      ,CastedToXml.value('(/x[y[1]/text()="derived_Attribute_4"]/y[2]/text())[1]','nvarchar(max)') AS  derived_Attribute_4
      ,CastedToXml.value('(/x[y[1]/text()="derived_Attribute_5"]/y[2]/text())[1]','nvarchar(max)') AS  derived_Attribute_5
FROM (VALUES(CAST(CONCAT('<x><y>',REPLACE(REPLACE(REPLACE(REPLACE(@rawData,'{"',''),'"}',''),'":"','</y><y>'),'","','</y></x><x><y>'),'</y></x>') AS XML)))A(CastedToXml);

The idea of the XPath is:

Find the <x> where the first <y>'s text() is the given name and read the second <y>'s text() as string.

Concerning cutting off the needed portion I provided a solution in my other answer.
Hint: reverse, left+charindex, reverse

Answer 3

Your input is valid JSON, so if you can use SQL Server 2016 or higher, you may try an approach, based on OPENJSON() and dynamic statement:

JSON

DECLARE @json nvarchar(max) = N'{
   "request_id":"801707",
   "final_decision":"PASS",
   "derived_Attribute_3":"PASS|Number of Total Active Institutions :3",
   "derived_Attribute_1":"PASS|Number of active MFI :0",
   "derived_Attribute_2":"PASS|Overdue Amount:0.0",
   "derived_Attribute_4":"PASS|Total Exposure + Applied Amount :76384.0",
   "derived_Attribute_5":"PASS|Write off amount:0.0",
   "cbResponseMsg":"Final Decision:PASS || Number of Total Active Institutions :3 || Number of active MFI :0 || Overdue Amount:0.0 || Total Exposure + Applied Amount :76384.0 || Write off amount:0.0"
}'

Statement:

-- Dynamic statement
DECLARE @stm nvarchar(max) = N'SELECT'
SELECT @stm = CONCAT(
   @stm,
   CASE WHEN @stm = 'SELECT' THEN N' ' ELSE N', ' END,
   RIGHT([value], LEN([value]) - CHARINDEX(':', [value])),
   N' AS [',
   [key],
   N']'
   )
FROM OPENJSON(@json)
WHERE [key] LIKE 'derived_Attribute_%'

-- Print and execute
PRINT @stm
EXEC sp_executesql @stm

Result:

---------------------------------------------------------------------------------------------------
derived_Attribute_3 derived_Attribute_1 derived_Attribute_2 derived_Attribute_4 derived_Attribute_5
---------------------------------------------------------------------------------------------------
3                   0                   0.0                 76384.0             0.0

Answer 4

What you've got is JSON, you know this seemingly. Starting with v2016 there is native JSON support, but with v2014 this will need some hacky string tricks (which can fail easily)...

My suggestion was to transform the JSON to attriute-centered XML:

declare @rawData nvarchar (max) =
'{"request_id":"801707","final_decision":"PASS","derived_Attribute_3":"PASS|Number of Total Active Institutions :3","derived_Attribute_1":"PASS|Number of active MFI :0","derived_Attribute_2":"PASS|Overdue Amount:0.0","derived_Attribute_4":"PASS|Total Exposure + Applied Amount :76384.0","derived_Attribute_5":"PASS|Write off amount:0.0","cbResponseMsg":"Final Decision:PASS || Number of Total Active Institutions :3 || Number of active MFI :0 || Overdue Amount:0.0 || Total Exposure + Applied Amount :76384.0 || Write off amount:0.0"}';

SELECT CAST(REPLACE(REPLACE(REPLACE(REPLACE(@rawData,'{"','<rawData '),'":"','="'),'","','" '),'"}','"/>') AS XML);

The result:

<rawData request_id="801707" 
         final_decision="PASS" 
         derived_Attribute_3="PASS|Number of Total Active Institutions :3" 
         derived_Attribute_1="PASS|Number of active MFI :0" 
         derived_Attribute_2="PASS|Overdue Amount:0.0" 
         derived_Attribute_4="PASS|Total Exposure + Applied Amount :76384.0" 
         derived_Attribute_5="PASS|Write off amount:0.0" 
         cbResponseMsg="Final Decision:PASS || Number of Total Active Institutions :3 || Number of active MFI :0 || Overdue Amount:0.0 || Total Exposure + Applied Amount :76384.0 || Write off amount:0.0" />

Now we can use a query along this to get hands on each single attribute's value.

SELECT B.* 
FROM (VALUES(CAST(REPLACE(REPLACE(REPLACE(REPLACE(@rawData,'{"','<rawData '),'":"','="'),'","','" '),'"}','"/>') AS XML)))A(CastedToXml)
CROSS APPLY(VALUES(CastedToXml.value('(/rawData/@request)[1]','int') 
                  ,CastedToXml.value('(/rawData/@final_decision)[1]','varchar(100)')
                  ,CastedToXml.value('(/rawData/@derived_Attribute_1)[1]','varchar(1000)') 
                  ,CastedToXml.value('(/rawData/@derived_Attribute_2)[1]','varchar(1000)') 
                  ,CastedToXml.value('(/rawData/@derived_Attribute_3)[1]','varchar(1000)') 
                  ,CastedToXml.value('(/rawData/@derived_Attribute_4)[1]','varchar(1000)') 
                  ,CastedToXml.value('(/rawData/@derived_Attribute_5)[1]','varchar(1000)') 
                  ,CastedToXml.value('(/rawData/@cbResponseMsg)[1]','varchar(1000)'))
            )B(request,final_decision,derived_Attribute_1,derived_Attribute_2,derived_Attribute_3,derived_Attribute_4,derived_Attribute_5,cbResponseMsg);

This allows to use simple string methods on each extracted string.

The final query

This leads us to the final query. See how I use REVERSE() to return the elements read from right to left. This allows to search for the first : and use LEFT() to cut this part off. For the final result we have to use REVERSE() on the cut string, too.

SELECT B.request_id 
      ,B.final_decision
      ,REVERSE(LEFT(B.derived_Attribute_1,CHARINDEX(':',B.derived_Attribute_1)-1)) AS derived_Attribute_1
      ,REVERSE(LEFT(B.derived_Attribute_2,CHARINDEX(':',B.derived_Attribute_2)-1)) AS derived_Attribute_2
      ,REVERSE(LEFT(B.derived_Attribute_3,CHARINDEX(':',B.derived_Attribute_3)-1)) AS derived_Attribute_3
      ,REVERSE(LEFT(B.derived_Attribute_4,CHARINDEX(':',B.derived_Attribute_4)-1)) AS derived_Attribute_4
      ,REVERSE(LEFT(B.derived_Attribute_5,CHARINDEX(':',B.derived_Attribute_5)-1)) AS derived_Attribute_5
      ,B.cbResponseMsg
FROM (VALUES(CAST(REPLACE(REPLACE(REPLACE(REPLACE(@rawData,'{"','<rawData '),'":"','="'),'","','" '),'"}','"/>') AS XML)))A(CastedToXml)
CROSS APPLY(VALUES(CastedToXml.value('(/rawData/@request_id)[1]','int') 
                  ,CastedToXml.value('(/rawData/@final_decision)[1]','varchar(100)')
                  ,REVERSE(CastedToXml.value('(/rawData/@derived_Attribute_1)[1]','varchar(1000)')) 
                  ,REVERSE(CastedToXml.value('(/rawData/@derived_Attribute_2)[1]','varchar(1000)')) 
                  ,REVERSE(CastedToXml.value('(/rawData/@derived_Attribute_3)[1]','varchar(1000)')) 
                  ,REVERSE(CastedToXml.value('(/rawData/@derived_Attribute_4)[1]','varchar(1000)')) 
                  ,REVERSE(CastedToXml.value('(/rawData/@derived_Attribute_5)[1]','varchar(1000)')) 
                  ,CastedToXml.value('(/rawData/@cbResponseMsg)[1]','varchar(1000)'))
            )B(request_id,final_decision,derived_Attribute_1,derived_Attribute_2,derived_Attribute_3,derived_Attribute_4,derived_Attribute_5,cbResponseMsg);

The result

+------------+----------------+---------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| request_id | final_decision | derived_Attribute_1 | derived_Attribute_2 | derived_Attribute_3 | derived_Attribute_4 | derived_Attribute_5 | cbResponseMsg                                                                                                                                                                      |
+------------+----------------+---------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 801707     | PASS           | 0                   | 0.0                 | 3                   | 76384.0             | 0.0                 | Final Decision:PASS || Number of Total Active Institutions :3 || Number of active MFI :0 || Overdue Amount:0.0 || Total Exposure + Applied Amount :76384.0 || Write off amount:0.0 |
+------------+----------------+---------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Answer 5

Ok

Here i am using the XMl way of splitting string in 2014 sql server.

1. first remove {}
2. Split string on ","
3. Split each substring on ":" to get Name field and Value field,a dn remove "

Now the string

"derived_Attribute_3":"PASS|Number of Total Active Institutions :3"

becomes

Name= derived_Attribute_3
Value= PASS|Number of Total Active Institutions :3

Finally i take this value, find the last ":" and split the string on that to get the value to the right which i "3" in the example

The solution looks like this:

declare @rawData nvarchar (max) =
'{"request_id":"801707","final_decision":"PASS","derived_Attribute_3":"PASS|Number of Total Active Institutions :3","derived_Attribute_1":"PASS|Number of active MFI :0","derived_Attribute_2":"PASS|Overdue Amount:0.0","derived_Attribute_4":"PASS|Total Exposure + Applied Amount :76384.0","derived_Attribute_5":"PASS|Write off amount:0.0","cbResponseMsg":"Final Decision:PASS || Number of Total Active Institutions :3 || Number of active MFI :0 || Overdue Amount:0.0 || Total Exposure + Applied Amount :76384.0 || Write off amount:0.0"}'
;with cte AS
(
/*Split in name and value by ":" and remove " */
select replace(left(Item,patindex('%":"%',ITem)),'"','') Name
,replace(Right(Item,len(item)-patindex('%":"%',ITem)-1),'"','') Value
from
(
/* use xml to split string by , and remove  {}*/
SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM 
      ( 
        SELECT x = CONVERT(XML, '<i>' 
          + replace(replace(REPLACE(@rawData, ',', '</i><i>') ,'}',''),'{','')
          + '</i>').query('.')
      ) AS a CROSS APPLY x.nodes('i') AS y(i)
) a
)
,unpacked AS
(
/* Finally look in the value column, and find the number to the right of : */
SELECT
Name
,right(value,charindex(':',reverse(Value))-1) Value
from cte
where charindex(':',reverse(Value)) between 1 and len(Value)-1
and name like 'derived_attribute%'

)
SELECT
     max(iif(name='derived_Attribute_1',value,null)) derived_Attribute_1
    ,max(iif(name='derived_Attribute_2',value,null)) derived_Attribute_2
    ,max(iif(name='derived_Attribute_3',value,null)) derived_Attribute_3
    ,max(iif(name='derived_Attribute_4',value,null)) derived_Attribute_4
    ,max(iif(name='derived_Attribute_5',value,null)) derived_Attribute_5
from unpacked

Answer 6

If you're using SQL 2014 or earlier then you'll need a string splitter function like the one from Jeff Moden

Then you'll need to split the string twice, once on "," and then on ":" And then use conditional aggregation

Like this

;WITH cteSplitVals
AS(
    SELECT CSV_ItemNumber=CSV.ItemNumber,COL.*
    FROM dbo.DelimitedSplit8K(replace(@rawData, '"', ''), ',') CSV
    CROSS APPLY
         dbo.DelimitedSplit8K(CSV.Item, ':') 
          COL
)
SELECT  
    derived_Attribute_1=MAX(CASE WHEN X.ColumnRow=4 THEN X.ColumnValue END),
    derived_Attribute_2=MAX(CASE WHEN X.ColumnRow=5 THEN X.ColumnValue END),
    derived_Attribute_3=MAX(CASE WHEN X.ColumnRow=3 THEN X.ColumnValue END),
    derived_Attribute_4=MAX(CASE WHEN X.ColumnRow=6 THEN X.ColumnValue END),
    derived_Attribute_5=MAX(CASE WHEN X.ColumnRow=7 THEN X.ColumnValue END)
FROM(
    SELECT  ColumnRow=P.CSV_ItemNumber, ColumnName=P.Item, ColumnValue = NULL
    FROM    cteSplitVals    P
    WHERE   P.CSV_ItemNumber IN (3, 4, 5, 6, 7)
    AND     P.ItemNumber IN (1)
    UNION
    SELECT  ColumnRow=P.CSV_ItemNumber, ColumnName=NULL, ColumnValue = P.Item
    FROM    cteSplitVals    P
    WHERE   P.CSV_ItemNumber IN (3, 4, 5, 6, 7)
    AND     P.ItemNumber IN (3)
) X

To get this

derived_Attribute_1 derived_Attribute_2 derived_Attribute_3 derived_Attribute_4 derived_Attribute_5
0                   0.0                 3                   76384.0                 0.0

ALl this is assuming the order of the string remains the same otherwise you'll need to use dynamic SQL