How to parse json data in SQL Server 2012?

Isaac your code is not working with not quoted values e.g. {"isAuthorized":"false","customerID":null}. I fixed this and your function should look like this.

ALTER FUNCTION [dbo].[JSON_VALUE]
(
    @JSON NVARCHAR(3000),
    @column NVARCHAR(3000)
)
RETURNS NVARCHAR(3000)
AS
BEGIN

DECLARE @value NVARCHAR(3000);
DECLARE @trimmedJSON NVARCHAR(3000);

DECLARE @start INT;
DECLARE @end INT;

set @start = PATINDEX('%' + @column + '":%',@JSON) + LEN(@column) + 2;
SET @trimmedJSON = SUBSTRING(@JSON, @start, LEN(@JSON));
Set @end = CHARINDEX(',',@trimmedJSON);
SET @value = REPLACE(SUBSTRING(@trimmedJSON, 0, @end),'"','');

-- -- why i add this ? because in case of "tag": null, previously retuned a string valued "null", whith this add retunr NULL sql value
IF upper(@value) = 'NULL'
    SET @value = NULL    

RETURN @value
END

I created a function compatible with SQL 2012 to take care of this

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Isaac Adams
-- Create date: 7/12/2018
-- Description: Give the JSON string and the name of the column from which you want the value
-- =============================================
CREATE FUNCTION JSON_VALUE
(
    @JSON NVARCHAR(3000),
    @column NVARCHAR(3000)
)
RETURNS NVARCHAR(3000)
AS
BEGIN

DECLARE @value NVARCHAR(3000);
DECLARE @trimmedJSON NVARCHAR(3000);

DECLARE @start INT;
DECLARE @length INT;

SET @start = PATINDEX('%' + @column + '":"%',@JSON) + LEN(@column) + 3;
SET @trimmedJSON = SUBSTRING(@JSON, @start, LEN(@JSON));
SET @length = PATINDEX('%", "%', @trimmedJSON);
SET @value = SUBSTRING(@trimmedJSON, 0, @length);

RETURN @value
END
GO

Isaac Adams function works for me on MS SQL Server 2012 and above. Here is my (hopefully) more refined approach:

CREATE FUNCTION JSON_VALUE
(
    @JSON NVARCHAR(MAX),
    @column NVARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @value NVARCHAR(MAX) = NULL;
    DECLARE @start INT;
    DECLARE @length INT;
    DECLARE @trimmedJSON NVARCHAR(MAX);

    -- Suche nach dem Anfang des JSON-Schlüssels und überprüfe, ob er existiert
    SET @start = PATINDEX('%"' + @column + '":"%', @JSON);

    IF @start > 0
    BEGIN
        -- Finde den Start des Wertes
        SET @start = @start + LEN(@column) + 3;
        SET @trimmedJSON = SUBSTRING(@JSON, @start, LEN(@JSON) - @start + 1);

        -- Finde das Ende des Wertes (bis zum nächsten Anführungszeichen)
        SET @length = PATINDEX('%"%', @trimmedJSON);

        -- Extrahiere den Wert, wenn er vorhanden ist
        IF @length > 0
        BEGIN
            SET @value = SUBSTRING(@trimmedJSON, 1, @length - 1);
        END
    END

    RETURN @value;
END
GO

Previous response (@Isaac and @Slaweek) have some case of failure:

1. If tag/column value is null (in json) return a string value "null" (and not the SQL NULL value) it's not an error, but we are working in sql...
2. If json is an element of an array and is betwwen {...} last tag/column return value + "...}"
3. Same of previous point if json is in "[...]"
4. Tag with "partial name" corresponding are misunderstanding (for example if in your json have "fiscal_address": ... and below "address": ... and you are searching for "address" return the value of "fiscal_address" because contain "address")
5. Commas in value are interpreted as a separated tag (example "price": "1,456.00")

Point nr 5 is not easy to solve: if the tag value is a text where comma/quote are important part of it ? (off course we can count quotes "... but we need to look at escape chars too)

For fix other points i modify in this way:

create or alter FUNCTION [dbo].[JSON_VALUE]
(
    @JSON NVARCHAR(3000),           -- contain json data
    @tag NVARCHAR(3000)             -- contain tag/column that you want the value
)
RETURNS NVARCHAR(3000)

AS
BEGIN

DECLARE @value NVARCHAR(3000);
DECLARE @trimmedJSON NVARCHAR(3000);

DECLARE @start INT
        , @end INT
        , @endQuoted int;

set @start = PATINDEX('%"' + @tag + '":%',@JSON) + LEN(@tag) + 3;
SET @trimmedJSON = SUBSTRING(@JSON, @start, LEN(@JSON));
Set @end = CHARINDEX(',',@trimmedJSON);
if (@end = 0)
    set @end = LEN(@trimmedJSON);
set @value = SUBSTRING(@trimmedJSON, 0, @end);

-- if is present a double-quote then the comma is not the tag-separator
if (len(@value) - len(replace(@value,'"','')) = 1)
begin
    set @endQuoted = CHARINDEX(',',  substring(@trimmedJSON, @end +1, LEN(@trimmedJSON) - @end +1))
    set @value = SUBSTRING(@trimmedJSON, 0, @endQuoted+@end);
end

SET @value = replace(@value,'"','');
-- remove last char if is a ]
IF (RIGHT(RTRIM(@VALUE), 1) = ']')
    SET @value = LEFT(RTRIM(@VALUE), LEN(RTRIM(@VALUE)) -1);
-- remove last char if is a }
IF (RIGHT(RTRIM(@VALUE), 1) = '}')
    SET @value = LEFT(RTRIM(@VALUE), LEN(RTRIM(@VALUE)) -1);

-- if tag value = "null" then return sql NULL value
IF UPPER(TRIM(@value)) = 'NULL'
    SET @value = NULL;

RETURN @value
END

>>> at JSON_VALUE function, at PATINDEX('%", "%', @trimmedJSON);

remove space from '%", "%'

if your JSON value is like

'{"street":"street1","street2":"street232423"}'

You can use JSON_VALUE(ColumnName,'$.Path') fro pars Json in TSQL

for example:

select JSON_VALUE(webaddress,'$.data.PID') as 'PID',
       JSON_VALUE(webaddress,'$.data.Status') as 'Status',
       JSON_VALUE(webaddress,'$.data.Name') as 'Name'
from test