Trimming the beginning and end of string in SQL

I have quite a long string that I want to trim the beginning and ending of. See a random example below:

{ “topic”: “birds”, “body”:”cool bird”, “attachment1″:”bird1”, “attachment2″:”anotherbird” }

My desired output:

“attachment1″:”bird1”, “attachment2″:”anotherbird”

I want to keep the attachments so I would have to trim everything up to the first attachment “attachment1” and get rid of the ‘}’ as well as the extra space.

FYI- I am using SQL Server. Any feedback or suggestions would be super helpful! Thank you. 🙂

EDIT: If ‘”attachment1″:’ cannot be found in the string, I’d like the value to return NULL instead of returning the original string. How would I modify the answer to incorporate this rule?

Answer

You can use:

select replace(stuff(str, 1, charindex('"attachment1":', str), ''), ' }', '')

Here is a db<>fiddle.

EDIT:

Probably the simplest solution to handle a missing "attachment" is to use case:

select (case when str like '%"attachment1"%' 
             then replace(stuff(str, 1, charindex('"attachment1":', str), ''), ' }', '')
        end)