Return SUBSTRING after specific word and end at first quote

postgres substring regex
postgresql substring from end
extractbefore matlab
extract part of string - matlab
postgres substring before character
postgres string functions
postgresql substring between two characters
matlab extract between

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.

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

sql, 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  Sometimes, more than finding a substring, we might need to get the string which is occurring after the substring has been found. Let’s discuss certain ways in which this task can be performed. The partition function can be used to perform this task in which we just return the part of partition occurring after the partition word.

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 

Extract substrings after specified positions, If str is a string array or a cell array of character vectors, then extractAfter the substring that begins after the position specified by startPos and ends with the last character of str . Starting in R2017a, you can create strings using double quotes. String that indicates the start of the substring to extract, specified as a string  If there is a requirement to retrieve the data from a column after a specific text, we can use a combination of TRIM, MID, SEARCH, LEN functions to get the output. Let us understand with an example: We have text in column A, and in column B, we want to get the result & cell D2 contains the criteria or lookup text. Refer below snapshot.

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            

Learning Processing: A Beginner's Guide to Programming Images, , indexOf(endTag, startIndex); IndexOf() can also take a second // If we don't find the end tag, if (endIndex == –1) return ""; return s.substring(startIndex,endIndex); } means: Find the first occurrence of the search String after this specified index. for stock quotes, count how many times the word “Flickr” appears on your favorite  You can tweak the string to split by - if you use "code : ", the second member of the returned array ( [1]) will contain "-1", using your example. Share a link to this answer. improve this answer. edited Feb 21 '13 at 9:34. answered Feb 21 '13 at 9:28. 789 silver badges. 937 bronze badges. Split only takes params [] char, it doesn't take params

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

https://docs.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];

Scripting InDesign with JavaScript, reserved words in JS—that is to say, words that JS understands in a particular Examples of these words are if, else, return, while, function, case, break, and var. isn\'t is required to ensure that the quote is not interpreted as a string delimiter. message = 'This is the first line\r'; message += 'of a message that ends up\r';  CHARINDEX (character to search, string to search) returns the position of the character in the string. If we want to extract before the character you would put the charindex as the number of characters and start position as 0 in the substring function. Usually we see lof of codes flying around for this extraction.Most of them difficult to remember.

Documentation: 9.1: String Functions and Operators, substring(string from pattern for escape), text, Extract substring matching SQL regular only the characters (a space by default) from the start/end/both ends of the string quote_ident(string text), text, Return the given string suitably quoted to be Quotes are added only if necessary (i.e., if the string contains non-​identifier  The Oracle SUBSTR () function extracts a substring from a string with various flexible options. The following illustrates the syntax of the Oracle SUBSTR () function: str is the string that you want to extract the substring. The data type of str can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. start_position is an integer that

Learning MySQL and MariaDB: Heading in the Right Direction with , That included using the TRIM() function to remove quotes from around the Instead, the bird species is given with its bird family in this format: This function will return the position in the string where the search parameter is found. point before the separator and we want to get the substring after the end of the separator. For instance, to get the last 4 characters from the end of a string, use this formula: =RIGHT(A2,4) Extract text from middle of string (MID) If you are looking to extract a substring starting in the middle of a string, at the position you specify, then MID is the function you can rely on.

PostgreSQL Substring, This tutorial shows you how to use PostgreSQL substring function to extract substring at the first character of the PostgreSQL string. we get PostgreS as the result. It must be wrapped inside escape characters followed by a double quote ("). I have a string that looks like this: GenFiltEff=7.092200e-01 Using bash, I would like to just get the number after the = character. Is there a way to do this?

Comments
  • Considering this is JSON, why not use OPENJSON? Parse and Transform JSON Data with OPENJSON (SQL Server).
  • There is a JSON function in our SQL server written by a previous colleague, but unfortunately it has no documentation and I was hoping to see if i could achieve this via Subtrings, but doesn't seem that its going to be that straight forward. I'll have a look at OPENJSON. Thanks :).
  • you've got a few troubles there - 'name' could exist in the data as well as a tag, so it could all blow up if a Mr Name came along, that sort of issue.
  • Yeah that is a good point...
  • Please note json support was only added to sql server in 2016 version. If you are working on an older version you might want to use an SqlClr function instead.
  • This worked in a way too, thank you.