1

The big picture is that I'm trying to query SQL report server execution logs to create a nice readable report of who used which reports and what parameters they used in each. The problem is that the parameters columns is just a long string.

So the more specific question I'm posing is what is the best way to take this string (example below) and parse everything before each ampersand into a new column?

Example string (So for this example string, I'd want new columns for: StartDate, EndDate, Program, Location, Status, Name):

StartDate=3%2F1%2F2021%2012%3A00%3A00%20AM&EndDate=3%2F31%2F2021%2012%3A00%3A00%20AM&Program=PRGRM1&Location=VN-South%20Main%20St&Status=Active&Name=Smith%2C%20Bob%20

And here's what I'm trying currently, but it's not really doing what I need, and I suspect I'm going down the wrong path here:

SELECT 
RTRIM(Substring(Parameters, CHARINDEX('&program=',Parameters, 1) + 9, 100)) as 'Program'

Any help is much appreciated... I'm just getting started with SQL and can't figure out how to best tackle this.

Jarrod
  • 91
  • 2
  • 3
  • 10
  • Does this answer your question? [How do I split a string so I can access item x?](https://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) – Chris Schaller May 13 '21 at 21:20

2 Answers2

2

This is a job for Sup...uh...XML

DECLARE @t TABLE( ID INT IDENTITY, data VARCHAR(250))
INSERT INTO @t(data) 
SELECT 'StartDate=3%2F1%2F2021%2012%3A00%3A00%20AM&EndDate=3%2F31%2F2021%2012%3A00%3A00%20AM&Program=PRGRM1&Location=VN-South%20Main%20St&Status=Active&Name=Smith%2C%20Bob%20'

SELECT  F1.ID, F1.data, O.splitdata
FROM (
        SELECT  *, CAST('<X>' + REPLACE(F.data, '&', '</X><X>') + '</X>' AS XML) AS xmlfilter
        FROM    @t F
    ) F1
CROSS APPLY(
               SELECT   fdata.D.value('.', 'varchar(50)') AS splitdata
               FROM F1.xmlfilter.nodes('X') AS fdata(D)
           ) O;

Maybe someone a little better with XML can explain how it all works.

enter image description here

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • Thanks, that definitely looks like a good direction to go in. I've been trying with that, and I can't figure out still how to get that to split into multiple columns. Like "StartDate" is a column, then "EndDate", "Program", etc. – Jarrod May 17 '21 at 13:22
1

TO get a single column with the data seperated by & you can use STRING_SPLIT

select * from string_split('StartDate=3%2F1%2F2021%2012%3A00%3A00%20AM&EndDate=3%2F31%2F2021%2012%3A00%3A00%20AM&Program=PRGRM1&Location=VN-South%20Main%20St&Status=Active&Name=Smith%2C%20Bob%20','&')

The result will be the folowing :

https://i.stack.imgur.com/6NThO.png

as for the next part, there might be some lead with the PIVOT

Karlheim
  • 121
  • 5