How to parse json data in SQL Server 2012?
19,684
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
Author by
Jui Test
Updated on June 04, 2022Comments
-
Jui Test almost 2 years
I am using SQL Server 2012.I have been assigned a task where one of my column (
JsonText
) of tableSample
contains json data. I want to pass parse that data and insert into columns of another table (Test
). I searched on net 'openjson' is supported in SQL Server 2016. How to do in SQL Server 2012?Table1 : Sample
Id JsonText Active
JsonText
webaddress?{'data':'{"PId": "XXXX","Status": "YES","Name":"XXX","Address":"XXXX","MobileNumber":"xxx"}'}
I am intrested only 'PID,Address,MobileNumber' columns not all.
Table Test structure like this
Id, PID, Address, MobileNumber
-
Igor Shubin almost 7 yearsYou can use it only with MSSQL 2016+ (docs.microsoft.com/en-us/sql/t-sql/functions/…)
-
Techy almost 4 yearsI am also using your same function,but I am not able to get the result.SELECT dbo.JSON_value('{street:"street1",street2:"street232423"}','street').It needs to give value stree1,but its gives empty value