Get a particular value from String

I want to remove particular value from my string. My string can be of any length. E.g.

string 1:

{“ABC”:1,”ABC_DT”:-1,”ABC_DBQty”:0,”ABC_DSQty”:0,”ABC_LMT”:1,”ABC_DT”:-1,”CTSD”:”TEST”,”SD”:1,”TE”:23}

string 2:

{“ABC”:1,”ABC_DT”:-1,”ABC_DBQty”:0,”ABC_DSQty”:0,”ABC_LMT”:1,”ABC_DT”:-1,”CTSD”:”TEST”,”TE”:23}

I want this 23 as a result. How can I get this in SQL Server 2014?

Answer

DECLARE @string VARCHAR(500) = '{"ABC":1,"ABC_DT":-1,"ABC_DBQty":0,"ABC_DSQty":0,"ABC_LMT":1,"ABC_DT":-1,"CTSD":"TEST","TE":23}'
SET @string = REVERSE(@string)

SELECT REVERSE(
                SUBSTRING(@string,2,CHARINDEX(':',@string)-1-1)
              )  --return: 23

Advertisement

Answer