I have a database that has a column with a long string and I'm looking for a way to extract just a certain portion of it.

Here is a sample:

{
    "vendorId": 53,
    "externalRef": "38828059 $567.82",
    "lines": [{
        "amount": 0,
        "lineType": "PURCHASE",
        "lineItemType": "INVENTORY",
        "inventory": {
            "cost": 0,
            "quantity": 1,
            "row": "6",
            "seatType": "CONSECUTIVE",
            "section": "102",
            "notes": "http://testurl/0F005B52CE7F5892 38828059 $567.82 ,special",
            "splitType": "ANY",
            "stockType": "ELECTRONIC",
            "listPrice": 0,
            "publicNotes": " https://brokers.123.com/wholesale/event/146489908 https://www.123.com/buy-event/4897564 ",
            "eventId": 3757669,
            "eventMapping": {
                "eventDate": "",
                "eventName": "Brandi Carlile: Beyond These Silent Days Tour",
                "venueName": "Gorge Amphitheatre"
            },
            "tickets": [{
                "seatNumber": 1527
            }]
        }
    }]
}

What I'm looking to extract is just http://testurl/0F005B52CE7F5892

Would someone be able to assist me with the syntax how to call my query that it will make a new temp column and give me just this extracted value for each row in this column?

I user SQL Server 2008 so some newer functions wont work for me.

CodePudding user response：

Upgrade your Sql Server to a supported version.

But till then, we pitty those who dare to face the horror of handling Json with only the old string functions.

select 
  [notes_url] = 
    CASE 
    WHEN [json_column] LIKE '%"notes": "http%'
    THEN substring([json_column], 
           patindex('%"notes": "http%', [json_column]) 10, 
             charindex(' ', [json_column] , 
               patindex('%"notes": "http%', [json_column]) 15)
               - patindex('%"notes": "http%', [json_column])-10)
    END
from [YourTable];

db<>fiddle here