Extract desired substring from a string

I want to remove all the raw data before and after the specific tag. e.g I have data in a variable like

declare @getdata varchar(max)= '
--uuid:674de154-e967-4153-84a4-549525203f52
Content-Type: application/xop+xml; charset=UTF-8; type="text/xml"
Content-Transfer-Encoding: binary
Content-ID: <root.message@cxf.apache.org
<LicensingReportProcessResult 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xmlns="http://www.acord.org/schema/data/draft/ReusableDataComponents/1" 
</LicensingReportProcessResult>
--uuid:674de154-e967-4153-84a4-549525203f52--'

I want to remove all raw data before <LicensingReportProcessResult tag and after tag I want result like this:

@getdata =
'<LicensingReportProcessResult 
Only the data between tag 
</LicensingReportProcessResult>'

I have done so far

select @getdata = right(@getdata, len(@getdata) - 
    charindex('<LicensingReportProcessResult', @getdata))

select @getdata = CONCAT('<',@getdata) 

select @getdata = left(@getdata, len(@getdata) - 
    charindex('</LicensingReportProcessResult>', @getdata))

Answer

Some fairly straight forward string searching and cutting will find your desired result:

-- Substring from the first tag to the last tag, including the last tag
select substring([data], StartTag, EndTag - StartTag + LenTag)
from (
  select @getdata [data]
    -- Find the start of the first tag
    , patindex('%<LicensingReportProcessResult%', @getdata) StartTag
    -- Find the start of the second tag
    , patindex('%</LicensingReportProcessResult>%', @getdata) EndTag
    -- Find the length of the tag
    , len('<LicensingReportProcessResult>') LenTag
) x;

Advertisement

Answer