CODEWITHSUNDEEP
jsonsql-serversql-server-2008-r2
Vijay
Vijay

Reputation: 69

Need one value in a big string in sqlserver

Need Sqlserver query.

Example 1:

{ "Id": 1785, "Type": 10, "Vendor": "vendor1", "Brand": "brand1", "LOB": 0, "Category": "Supported", "Name": "Security Bulletin", "MarketVersion": "WW17-150", "InternalVersion": "1.0", "Edition": "", "Role": "", "CDIVersion": "", "BuildVersion": "", "Patch": "", "Hotfix": "", "Arch": "x86", "ReleaseDate": "2017-09-29", "Released": 1, "Locale": "en-us", "Tag": "", "ConfigDetails": "", "Description": "Security-Only update for Windows (KB4038779, KB4038786, KB4038793)", "Notes": "Wonderware Security Bulletin WW17-085", "UpdateDate": "2017-09-29", "ExceptionStatement": "dddddd"}

Example 2:

{ "Id": 783, "Type": 10, "Vendor": "vendor1", "Brand": "brand1", "LOB": 0, "Category": "Supported", "Name": "Security Bulletin", "MarketVersion": "WW17-085", "InternalVersion": "1.0", "Edition": "", "Role": "", "CDIVersion": "", "BuildVersion": "", "Patch": "", "Hotfix": "", "Arch": "x86", "ReleaseDate": "2017-09-29", "Released": 1, "Locale": "en-US", "Tag": "", "ConfigDetails": "", "Description": "Security-Only update for Windows (KB4038779, KB4038786, KB4038793)", "Notes": "Note: All Microsoft KB#s listed in the table above are supported with Wonderware products listed under Mainstream Support. *Note: KB4038779 is Security-Only update for Windows 7 SP1 and Windows Server 2008 R2 SP1. *Note: KB4038786 is Security-Only update for Windows Server 2012. *Note: KB4038793 is Security-Only update for Windows 8.1 and Windows Server 2012 R2.", "UpdateDate": "2017-09-29", "ExceptionStatement": "dddddd"}

Example 3:

{ "Id": 1859, "Type": 1, "Vendor": "Microsoft", "Brand": "", "LOB": 0, "Category": "", "Name": "Windows", "MarketVersion": "30", "InternalVersion": "", "Edition": "Embedded Standard", "Role": "", "CDIVersion": "", "BuildVersion": "", "Patch": "SP1", "Hotfix": "", "Arch": "x86", "ReleaseDate": "2017-11-21", "Released": 1, "Locale": "en-us", "Tag": "", "ConfigDetails": "", "Description": "desc", "Notes": "notes", "MsSecurityLink": "", "InformationLink": "", "UpdateDate": "1900-01-01", "ExceptionStatement": ""}

In the above json string there is 'Type' attribute. I need the value of the Type. For example1 the output should be 10 and in the second example also the output should be 10 and in the third example the output should be 1.

Please help the sqlserver query.

Upvotes: 0

Views: 57

Answers (1)

Sergey Menshov
Sergey Menshov

Reputation: 3906

Try the following

DECLARE @json varchar(1000)='{ "Id": 1785, "Type": 10, "Vendor": "vendor1", ...}'

DECLARE @pos1 int=NULLIF(CHARINDEX('"Type": ',@json),0)+LEN('"Type": ')
DECLARE @pos2 int=CHARINDEX(',',@json,@pos1)

SELECT @pos1,@pos2,CAST(SUBSTRING(@json,@pos1,@pos2-@pos1) AS int)

In the query

SELECT pos1,pos2,CAST(SUBSTRING(json,pos1,pos2-pos1) AS int) [Type],json
FROM
  (
    SELECT
      NULLIF(CHARINDEX('"Type": ',json),0)+LEN('"Type": ') pos1,
      CHARINDEX(',',json,NULLIF(CHARINDEX('"Type": ',json),0)+LEN('"Type": ')) pos2,
      json
    FROM
      (
        SELECT '{ "Id": 1785, "Type": 10, "Vendor": "vendor1", ... }' json
        UNION ALL SELECT '{ "Id": 783, "Type": 10, "Vendor": "vendor1", ...}'
        UNION ALL SELECT '{ "Id": 1859, "Type": 1, "Vendor": "Microsoft", ...}'
        UNION ALL SELECT '{"IdLeft": 255, "TypeLeft": 0, "IdRight": 284, "TypeRight": 0, "RelationshipType": 1, "Description": "" }'
      ) q
  ) q

Upvotes: 1

Related Questions