How to split the values from a string field in SQL

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

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

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

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 |
+------------+----------------+---------------------+---------------------+---------------------+---------------------+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

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