Continuation of the previous question

There is a field that stores json text. Inside there is a variable id {"Id":25680,"Code":.... It is necessary to take from json Id of the object and put the value in the field. How to cut the Id value? Number of digits is different.

JSON_VALUE not supported in SQL Server 2014

    2 answers 2

    It is possible through substring if the text has the same structure:

     create table table1 (json varchar (200)); insert into table1 values ('{"Id":25680,"Code":....') select substring (json,charindex (':',json)+1,charindex (',',json)-charindex (':',json)-1) from table1

    Feeddle

      Retrieve the specified key (Id, Code or some other):

       DECLARE @t VARCHAR(2000) = '{"Id":25680,"Code":...,' ,@key VARCHAR(32) SET @key = 'Id' SELECT SUBSTRING( @t, PATINDEX('%"'+@key+'":%', @t) + LEN('"'+@key+'":'), CHARINDEX(',', @t, PATINDEX('%"'+@key+'":%', @t) + LEN('"'+@key+'":')) - (PATINDEX('%"'+@key+'":%', @t) + LEN('"'+@key+'":')) )