Search code examples
sql-serversql-server-2014

Parse the JSON text from DB column nvarchar

I have value in a column which stores the data as an json object .

Below is an example how the value is stored, I know the name of which I have to get the value over here

{"Name":"Today Date","value":"02/23/2017"},{"Name":"Exp Date","value":"02/23/2016"}

I want the value of name "Today Date" over here.

I can't use json parse as we are still using sql server 2014.

Currently with a hint from r41n's answer, I did the below:

select SUBSTRING
( '{"Name":"Today Date","value":"02/23/2017"},{"Name":"Exp Date","value":"02/23/2016"}',

PATINDEX('%{"Name":"Today Date","value":"%', '{"Name":"Today Date","value":"02/23/2017"},{"Name":"Exp Date","value":"02/23/2016"}') + len ('{"Name":"Today Date","value":"'),
 10)

If there are any other alternative solutions, please let me know.

Solution

  • PATINDEX + SUBSTRING

    It's slow and kinda ugly, but you could go and use PATINDEX like so:

    PATINDEX('%{"Name":"Today Date","value":"%', JSONColumn)

    This would give you the starting-position in JSONColumn where string starts. You could then go and use SUBSTRING(JSONColumn, 30, 10) to get only the date.

    This obviously doesn't work if the date or JSON data is not constant or changes from one instance to another.

    I didn't test this, but used it before.