CODEWITHSUNDEEP
jsonsql-servert-sqlsql-server-2014
user5171795
user5171795

Reputation: 115

Extracting a substring after finding a different substring

I've been playing around with Substring, left, right, charindex and can't quite get this to work

If this is the value in column name 'Data' (this is all one line)

{"email":{"RecipientId":"usertest","RecipientEmail":"[email protected]","Subject":"This is a test subject heading","RecipientSubject":"A recipient subject"}}

How do I do a SELECT statement to find the 'Subject' heading and then get the data 'This is a test subject'? The Subject value is different for every record so I just can't look for 'This is a test subject'.

So the end result should be This is a test subject for that SELECT result

Upvotes: 0

Views: 64

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

The plain and easy-cheesy approach is this:

SELECT SUBSTRING(
                 t.YourString
                ,A.StartPosition
                ,CHARINDEX('"'
                          ,t.YourString
                          ,A.StartPosition+1) - A.StartPosition
                 )
FROM @dummyTable t
CROSS APPLY(SELECT CHARINDEX('"Subject":"',t.YourString)+11) A(StartPosition)

I use APPLY to calculate a value and use it like you'd use a variable. The idea is: Find the starting point and look for the closing quote from there. But this will break, whenever the content includes an (escaped) quote like in

"Subject":"This is \"quoted\" internally"

A more generic approach

Starting with v2016 JSON-support was introduced. With this (or a higher) version this is really simple:

Use this mockup-table for testing

DECLARE @dummyTable TABLE (YourString VARCHAR(1000));
INSERT INTO @dummyTable VALUES('{"email":{"RecipientId":"usertest","RecipientEmail":"[email protected]","Subject":"This is a test subject heading","RecipientSubject":"A recipient subject"}}');

--The OPENJSON-method will read this for you:

SELECT JsonContent.*
FROM @dummyTable t
CROSS APPLY OPENJSON(t.YourString,'$.email') 
WITH(RecipientId VARCHAR(100)
    ,RecipientEmail VARCHAR(100)
    ,[Subject] VARCHAR(100)
    ,RecipientSubject VARCHAR(100)) JsonContent;

But with a lower version you will need to trick this out. It is the easiest, to tranform JSON to attribute centered XML like here:

<email RecipientId="usertest" RecipientEmail="[email protected]" Subject="This is a test subject heading" RecipientSubject="A recipient subject" />

We can achieve this by some string methods and I must warn you, that there are several pit-falls with forbidden characters and other stuff... Just try it out:

SELECT Casted.ToXml.value('(/email/@RecipientId)[1]','varchar(1000)') AS RecipientId
      ,Casted.ToXml.value('(/email/@RecipientEmail)[1]','varchar(1000)') AS RecipientEmail
      ,Casted.ToXml.value('(/email/@Subject)[1]','varchar(1000)') AS [Subject]
      ,Casted.ToXml.value('(/email/@RecipientSubject)[1]','varchar(1000)') AS RecipientSubject
      ,Casted.ToXml.query('.') LookHowThisWasTransformed
FROM @dummyTable t 
CROSS APPLY
(
    SELECT CAST(CONCAT('<email '
                      ,REPLACE(REPLACE(REPLACE(REPLACE(t.YourString,'{"email":{"',''),'}}',''),'","','" '),'":"',' ="')
                      ,' />') AS XML)
) Casted(ToXml);

Upvotes: 1

MJoy
MJoy

Reputation: 1369

The following query should do what you want:

declare @string varchar(max);
set @string = '{"email":{"RecipientId":"usertest","RecipientEmail":"[email protected]","Subject":"This is a test subject heading","RecipientSubject":"A recipient subject"}}';

select substring(@string,charindex('"Subject":',@string)+11,charindex('"RecipientSubject"',@string)-charindex('"Subject"',@string)-13);

Upvotes: 1

Related Questions