SQL - String Manipulation

Question

Context: I have a view in SQL Server that tracks parameters a user inputs when they run an SSRS report (ReportServer.dbo.ExecutionLog). About 50 report parameters are saved as a string in a single column with ntext datatype. I would like to break this single column up into multiple columns for each parameter.

Details: I query the report parameters like this:

SELECT ReportID, [Parameters]
FROM ReportServer.dbo.ExecutionLog
WHERE ReportID in (N'redacted')
and [Status] in (N'rsSuccess')
ORDER BY TimeEnd DESC

And here's a small subset of what the results look like:

alpha=123&bravo=9%2C33%2C76%2C23&charlie=91&delta=29&echo=11%2F2%2F2018%2012%3A00%3A00%20AM&foxtrot=11%2F1%2F2030%2012%3A00%3A00%20AM

Quesitons:

How can I get the results to look like this:

SQL Server 2017 is Python friendly. Is Python a better language to use in this scenario just for parsing purposes?

I've seen similar topics posted here, here & here. The parameters are dynamic so parsing via SQL string functions that involve counting characters doesn't apply. This question is relevant to more people than just me because there's a large population of people using SSRS. Tracking & formatting parameters in a more digestible way is valuable for all users of SSRS.

Answer 1

Split the string on the ampersand character.

Further split each row into two columns on the equals character.

In the second column, replace %2C with the comma character, and %2F with the forward-slash character, and so on with any other replacements as needed.

Use a dynamic-pivot to query the above in the format that you want.

Answer 2

Here is a way using the built in STRING_SPLIT. I'm just not sure what the logic is for the stuff AFTER the date, so I would discarded it but I left it for you to decide.

DEMO

declare @table table (ReportID int identity(1,1),  [Parameters] varchar(8000))
insert into @table
values
('alpha=123&bravo=9%2C33%2C76%2C23&charlie=91&delta=29&echo=11%2F2%2F2018%2012%3A00%3A00%20AM&foxtrot=11%2F1%2F2030%2012%3A00%3A00%20AM')
,('alpha=457893&bravo=9%2C33%2C76%2C23&charlie=91&delta=29&echo=11%2F2%2F2018%2012%3A00%3A00%20AM&foxtrot=11%2F1%2F2030%2012%3A00%3A00%20AM')

select 
    ReportID
    ,[Parameters]
    ,alpha = max(iif(value like 'alpha%',substring(value,charindex('=',value) + 1,99),null))
    ,bravo = max(iif(value like 'bravo%',substring(value,charindex('=',value) + 1,99),null))
    ,charlie = max(iif(value like 'charlie%',substring(value,charindex('=',value) + 1,99),null))
    ,delta = max(iif(value like 'delta%',substring(value,charindex('=',value) + 1,99),null))
    ,echo = max(iif(value like 'echo%',substring(value,charindex('=',value) + 1,99),null))
    ,foxtrot = max(iif(value like 'foxtrot%',substring(value,charindex('=',value) + 1,99),null))
from @table
cross apply string_split(replace(replace([Parameters],'%2C',','),'%2F','/'),'&')
group by ReportID, [Parameters]

Or, if they aren't static you can use a dynamic pivot. It'll take some massaging to get your columns in the correct order.

DEMO

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(substring([value],0,charindex('=',[value]))) 
                from myTable
                cross apply string_split(replace(replace([Parameters],'%2C',','),'%2F','/'),'&')
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


select @cols

set @query = 'SELECT ReportID, ' + @cols + ' from 
            (
                select ReportID
                    , ColName = substring([value],0,charindex(''='',[value]))
                    , ColVal = substring([value],charindex(''='',[value]) + 1,99)
                from myTable
                cross apply string_split(replace(replace([Parameters],''%2C'','',''),''%2F'',''/''),''&'')
           ) x
            pivot 
            (
                 max(ColVal)
                for ColName in (' + @cols + ')
            ) p '

execute(@query)

Answer 3

Here's a method that starts with a lot of replaces.
To url-decode the string and transform it into an XML type.

Then it uses the XML functions to get the values for the columns.

Example snippet:

declare @Table table ([Parameters] varchar(200));

insert into @Table ([Parameters]) values
('alpha=123&bravo=9%2C33%2C76%2C23&charlie=91&delta=29&echo=11%2F2%2F2018%2012%3A00%3A00%20AM&foxtrot=11%2F1%2F2030%2012%3A00%3A00%20AM');

select 
x.query('/x[key="alpha"]/val').value('.', 'int') as alpha,
x.query('/x[key="bravo"]/val').value('.', 'varchar(30)') as bravo,
x.query('/x[key="charlie"]/val').value('.', 'varchar(30)') as charlie,
x.query('/x[key="delta"]/val').value('.', 'varchar(30)') as delta,
convert(date, x.query('/x[key="echo"]/val').value('.', 'varchar(30)'), 103)as echo,
convert(date, x.query('/x[key="foxtrot"]/val').value('.', 'varchar(30)'), 103) as foxtrot
from @Table
cross apply (select cast('<x><key>'+ 
  replace(replace(replace(replace(replace(
    replace([Parameters], 
       '%2C',','), 
       '%2F','/'), 
       '%20',' '), 
       '%3A',':'),
       '=','</key><val>'), 
       '&','</val></x><x><key>') 
     +'</val></x>' as XML) as x) ca

Test on db<>fiddle here