Reputation: 325

Return SUBSTRING after specific word and end at first quote

I'm trying to return a SUBSTRING of data before and after a specific word. e.g. select everything after name until the quote("). I will need to repeat this for each required field in the string.

Here is an example note value that I need to extract the data from:

[
   {
      "code":"0123456",
      "name":"example",
      "table":"exampletable",
      "addedby":"exampleperson",
      "dateadded":1520333304750,
      "qualifier":[
         {
            "name":"Qualifier",
            "value":"examplevalue",
            "code":"123456",
            "prefix":"[?] "
         }
      ],
      "prefix":"[?] ",
      "suffix":""
   },
   {
      "code":"68566005",
      "name":"example2",
      "table":"exampletable2",
      "addedby":"exampleperson2",
      "dateadded":1519874550441,
      "qualifier":[
         {
            "name":"Qualifier",
            "value":"examplevalue2",
            "code":"415684004 ",
            "prefix":"[?] "
         }
      ],
      "prefix":"[?] ",
      "suffix":""
   }
]

Here is my attempt to extract the name from this:

select SUBSTRING(NoteValue, CHARINDEX('name', NoteValue), LEN(NoteValue))NoteValue

It starts at the ' name" ' portion of the the notekey, but I need to figure out how to end it at a specific point. The end result is that i'll be able to select each value for each field from the string.

The part that makes this slightly more complicated is that there may be multiple name fields that I need to extract from the string.

Hope my question makes sense. Thanks.

Upvotes: 3

Answers (4)

Tim Mylott

Reputation: 2723

If you are SQL server version 2016+ openjson will do the trick nicely

https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-2017

One challenge would be the nested array in this json, here's an example:

    DECLARE @json NVARCHAR(MAX);

    SET @json = N'
            [
       {
          "code":"0123456",
          "name":"example",
          "table":"exampletable",
          "addedby":"exampleperson",
          "dateadded":1520333304750,
          "qualifier":[
             {
                "name":"Qualifier",
                "value":"examplevalue",
                "code":"123456",
                "prefix":"[?] "
             }
          ],
          "prefix":"[?] ",
          "suffix":""
       },
       {
          "code":"68566005",
          "name":"example2",
          "table":"exampletable2",
          "addedby":"exampleperson2",
          "dateadded":1519874550441,
          "qualifier":[
             {
                "name":"Qualifier",
                "value":"examplevalue2",
                "code":"415684004 ",
                "prefix":"[?] "
             }
          ],
          "prefix":"[?] ",
          "suffix":""
       }
    ]
            ';

    --Top level array
    SELECT [code]
         , [name]
         , [table]
         , [addedby]
         , [dateadded]
         , [prefix]
         , [suffix]
    FROM
           OPENJSON(@json, '$')
               WITH (
                        [code] NVARCHAR(200) '$.code'
                      , [name] NVARCHAR(200) '$.name'
                      , [table] NVARCHAR(200) '$.table'
                      , [addedby] NVARCHAR(200) '$.addedby'
                      , [dateadded] NVARCHAR(200) '$.dateadded'
                      , [prefix] NVARCHAR(200) '$.prefix'
                      , [suffix] NVARCHAR(200) '$.suffix'
                    );

    --Get data from the nested array qualifier
    SELECT [b].[name]
         , [b].[value]
         , [b].[code]
         , [b].[prefix]
    FROM   OPENJSON(@json, '$') [a]
    CROSS APPLY
           OPENJSON([a].[Value], '$.qualifier') --this gets you into the nested array
               WITH (
                        [name] NVARCHAR(200) '$.name'
                      , [value] NVARCHAR(200) '$.value'
                      , [code] NVARCHAR(200) '$.code'
                      , [prefix] NVARCHAR(200) '$.prefix'
                    ) [b];

    --if all you want is the name column from both the top level array AND the nested array, you could use a union all:
    SELECT [name]
    FROM
           OPENJSON(@json, '$')
               WITH (
                        [name] NVARCHAR(200) '$.name'
                    )
    UNION ALL
    SELECT [b].[name]
    FROM   OPENJSON(@json, '$') [a]
    CROSS APPLY
           OPENJSON([a].[Value], '$.qualifier') --this gets you into the nested array
               WITH (
                        [name] NVARCHAR(200) '$.name'
                    ) [b];

Upvotes: 0

Alan Burstein

Reputation: 7928

You can use a splitter, I'd go with delimitedSplit8K.

Solution:

DECLARE @string VARCHAR(8000) = 
'[
   {
      "code":"0123456",
      "name":"example",
      "table":"exampletable",
      "addedby":"exampleperson",
      "dateadded":1520333304750,
      "qualifier":[
         {
            "name":"Qualifier",
            "value":"examplevalue",
            "code":"123456",
            "prefix":"[?] "
         }
      ],
      "prefix":"[?] ",
      "suffix":""
   },
   {
      "code":"68566005",
      "name":"example2",
      "table":"exampletable2",
      "addedby":"exampleperson2",
      "dateadded":1519874550441,
      "qualifier":[
         {
            "name":"Qualifier",
            "value":"examplevalue2",
            "code":"415684004 ",
            "prefix":"[?] "
         }
      ],
      "prefix":"[?] ",
      "suffix":""
   }
]';

SELECT f.nodeName, f.nodeValue
FROM 
(
  SELECT 
    s.*, 
    nodeName  = LAG(s.item,1)  OVER (ORDER BY s.itemNumber),
    nodeValue = LEAD(s.item,1) OVER (ORDER BY s.itemNumber)
  FROM samd.delimitedSplitAB8K(@string,'"') s
) f
WHERE item = ':';

Results:

nodeName          nodeValue
----------------- ----------------------------
code              0123456
name              example
table             exampletable
addedby           exampleperson
name              Qualifier
value             examplevalue
code              123456
prefix            [?] 
prefix            [?] 
suffix            
code              68566005
name              example2
table             exampletable2
addedby           exampleperson2
name              Qualifier
value             examplevalue2
code              415684004 
prefix            [?] 
prefix            [?] 
suffix

Upvotes: 1

Sandip Chavan

Reputation: 39

try this :

SELECT 
    substring( SUBSTRING(NoteValue, CHARINDEX('name', NoteValue)+4,LEN(NoteValue)),1, charindex('(").',NoteValue) )    
FROM ( 

SELECT  '
[{"code":"0123456" ,"name":"example" ,"table":"exampletable" 
,"addedby":"exampleperson" ,"dateadded":1520333304750, "qualifier":[{"name":"Qualifier" ,"value":"examplevalue" 
,"code":"123456", "prefix":"[?] "}] ,"prefix":"[?] " ,"suffix":""} ,{"code":"68566005" ,"name":"example2" ,"table":"exampletable2"
, "addedby":"exampleperson2" ,"dateadded":1519874550441, "qualifier":[{"name":"Qualifier" ,"value":"examplevalue2" ,"code":"415684004 
","prefix":"[?] "}] ,"prefix":"[?] " ,"suffix":""}]' NoteValue 
 ) s

Upvotes: 1

KeithL

Reputation: 5594

I did some dancing, but here is a solution using string functions:

DECLARE @json NVARCHAR(MAX)
    ,@namestart int
    ,@Q1 int
    ,@Q2 int
    ,@After varchar(100)
    ,@before varchar(100)

SET @json='{"code":"0123456" ,"name":"example" ,"table":"exampletable" ,"addedby":"exampleperson" ,"dateadded":1520333304750, "qualifier":[{"name":"Qualifier" ,"value":"examplevalue" ,"code":"123456", "prefix":"[?] "}] ,"prefix":"[?] " ,"suffix":""} ,{"code":"68566005" ,"name":"example2" ,"table":"exampletable2", "addedby":"exampleperson2" ,"dateadded":1519874550441, "qualifier":[{"name":"Qualifier" ,"value":"examplevalue2" ,"code":"415684004 ","prefix":"[?] "}] ,"prefix":"[?] " ,"suffix":""';

set @namestart = charindex('"name"',@json)

set @Q1 = CHARINDEX('"',@json, @namestart + len('"name"'))

set @Q2 =  CHARINDEX('"',@json ,  @Q1+1)

set @After = substring(@json, @q1+1,@q2-@q1-1)

--select @namestart , @Q1 ,@Q2, @After

declare @priorPiece varchar(100)

set @priorPiece = left(@json,@namestart-1)

set @Q1 = charindex('"',reverse(@priorPiece ))
set @q2 = charindex('"',reverse(@priorPiece ),@q1+1)
set @before = reverse(substring(reverse(@priorPiece ),@q1+1,@q2-@q1-1))

select @before,@after

Upvotes: 2

Return SUBSTRING after specific word and end at first quote

Answers (4)

Related Questions