Parse utm parameters in sql query

Question

I have a table in my database that tracks visitors page views and I am trying to pull out the utm tracking parameters from the url

https://www.example.com/?utm_source=Adwords&utm_medium=ppc&utm_campaign=PGLawBrand&utm_ID=18075&mh_matchtype=e&mh_keyword=university%20of%20law

utm_medium = ppc
utm_source = Adwords
utm_campaign = PGLawBrand
utm_id = 18075

You can use this script to create table

CREATE TABLE [ExampleURLs]
(
   URL_ID      INTEGER  NOT NULL PRIMARY KEY, 
   CompleteURL VARCHAR(375) NOT NULL
);

INSERT INTO [ExampleURLs] (URL_ID, CompleteURL) VALUES (1, 'https://www.example.com/study/postgraduate/business/msc-global-accounting/?utm_source=GoogleAdsGUS&utm_medium=ppc&utm_campaign=ULAW_BS_PG_Display_MSc_Global_Accounting_Pakistan&mh_matchtype=&mh_keyword=&mh_adgroupid=121117410605&mh_network=d&gclid=Cj0KCQiAubmPBhCyARIsAJWNpiMZQRbISRdfxttoQiUSnsORholTZ2vi30TNbtU2QOLpUacKgwIfVrYaApSrEALw_wcB');
INSERT INTO [ExampleURLs] (URL_ID, CompleteURL) VALUES (2, 'https://www.example.com/study/postgraduate/business/msc-business-intelligence-and-analytics/?utm_source=GoogleAdsGUS&utm_medium=ppc&utm_campaign=ULAW_BS_PG_Display_MSc_Business_Intelligence_and_Analytics_Nigeria&mh_matchtype=&mh_keyword=&mh_adgroupid=114552103120&mh_network=d&gclid=EAIaIQobChMIrrO8rc6A9gIVEgTTCh39Bg6nEAEYASAAEgI_MfD_BwE');
INSERT INTO [ExampleURLs] (URL_ID, CompleteURL) VALUES (3, 'https://questions.example.com/artificial-intelligence/?utm_source=Facebook&utm_medium=Social&utm_campaign=PFBINTLTA&utm_ID=14376&fbclid=IwAR1eUWk8F2UyGItSMa0uGcm3z6QgZwf8rKlr8Ju6_LCI8AyDp32dpCIvZaE_aem_Ad2i5t7WhLmVaxblXpXj7JozaJ1FWFqzVQ9VBPr6OK_9qYfsk5RPY9jxV8AA87qROLo6cjGQLr8FB6wVJBipoyUYCw6aDnfjeujS1xE-x_4AvASvAgVeeTYKX0G20GcAW34');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (4,'https://www.example.com/study/postgraduate/law/pgdl//?utm_source=Adwords&utm_medium=ppc&utm_campaign=INTLTAJan22PPC&utm_ID=21675&mh_matchtype=e&mh_keyword=law%20conversion%20course&mh_adgroupid=129115495162&mh_network=g&gclid=EAIaIQobChMI94ajoLGl9QIVj-vtCh04mw4mEAAYAiAAEgKstfD_BwE');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (5,'https://www.example.com/study/postgraduate/business/msc-project-management/?utm_source=GoogleAdsGUS&utm_medium=ppc&utm_campaign=ULAW_BS_PG_Display_MSc_Project_Management_Nigeria&mh_matchtype=&mh_keyword=&mh_adgroupid=112995934780&mh_network=d&gclid=Cj0KCQiAuP-OBhDqARIsAD4XHpce_pD0g4mASPP4W6Ly-0Lw-YV7FjdKs7FjpEF66M01HiCcfKrKTWgaAj8FEALw_wcB');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (6,'https://mike-diaz006.medium.com/what-i-learned-at-work-this-week-more-difficult-sql-parsing-e5797bacf724?utm_id=34566&utm_source=test');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (7,'https://www.example.com/events/event-booking/?id=53efe5ff-e0ca-ec11-a7b6-0022481a8756&utm_source=Facebook&utm_medium=Social&utm_campaign=TGM_UG_FB_BID_Brand__OpenDays&utm_ID=23580');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (9,'https://www.example.com/events/event-booking/?id=53efe5ff-e0ca-ec11-a7b6-0022481a8756&utm_source=Facebook&utm_medium=Social&utm_campaign=TGM_UG_FB_BID_Brand__OpenDays&utm_ID=23580');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (11,'https://estore.example.com/sqe-law-essentials-online');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (13,'https://www.example.com/events/event-booking/?id=8708f49b-b3e6-ea11-a817-000d3a86b410');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (15,'https://www.example.com/?utm_source=Adwords&utm_medium=ppc&utm_campaign=PGLawBrand&utm_ID=18075&mh_matchtype=e&mh_keyword=university%20of%20law&mh_adgroupid=108858981997&mh_network=g&gclid=CjwKCAiAv4n9BRA9EiwA30WND68-M6ZIRf1tWRTBxM5iGffTHthufZdEewNLOWkHgM8TXHADNmVVNxoClbwQAvD_BwE');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (17,'https://www.example.com/study/postgraduate/business/msc-strategic-business-management/?utm_source=GoogleAdsGUS&utm_medium=ppc&utm_campaign=ULAW_BS_PG_Display_MSc_Strategic_Business_Management_Nigeria&mh_matchtype=&mh_keyword=&mh_adgroupid=111617643789&mh_network=d&gclid=EAIaIQobChMIxNXQidLn9AIV8ewRCB3YDgC3EAEYASAAEgK1ofD_BwE');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (19,'https://www.example.com//?utm_source=Adwords&utm_medium=ppc&utm_campaign=IntGenericItaly&utm_ID=17470&mh_matchtype=b&mh_keyword=law%20school%20uk&mh_adgroupid=99633500297&mh_network=g&gclid=Cj0KCQiAuP-OBhDqARIsAD4XHpemygNONpKbf4T7FOrndG7pgPpO2oOOt2lBcTx0AZ5Be4mUHZI68kcaAlZmEALw_wcB');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (21,'https://www.example.com/');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (23,'https://www.example.com/study/online/?utm_source=GoogleAdsGUS&utm_medium=ppc&utm_campaign=ULAW_BS_Online_PG_Nigeria_Search&mh_matchtype=&mh_keyword=&mh_adgroupid=125774081747&mh_network=s&gclid=EAIaIQobChMIicOj-_y69wIVfIBQBh2Glw3cEAAYASAAEgJRTfD_BwE');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (25,'https://www.example.com/study/undergraduate/business/bsc-hons-international-business-management/?utm_source=GoogleAdsGUS&utm_medium=ppc&utm_campaign=ULAW_BS_UG_Display_Int_BA_Business_Mgmt_Nigeria&gclid=EAIaIQobChMI0orhttHv8AIV1dTtCh15XAusEAEYASAAEgLCrPD_BwE');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (27,'https://www.example.com/study/postgraduate/business/msc-business-intelligence-and-analytics/?utm_source=GoogleAdsGUS&utm_medium=ppc&utm_campaign=ULAW_BS_PG_Display_MSc_Business_Intelligence_and_Analytics_Nigeria&mh_matchtype=&mh_keyword=&mh_adgroupid=114552103080&mh_network=d&gclid=Cj0KCQiA_8OPBhDtARIsAKQu0gZweT8ZKN7CfuoPpppKkT0oiq1iDio18JqFswPV_SK74yfZQqy_lpwaAurYEALw_wcB');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (29,'https://www.example.com/employability/career-finder/paralegal/');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (31,'https://www.example.com/study/postgraduate/business/msc-global-accounting/#start-dates');
INSERT INTO [ExampleURLs](URL_ID,CompleteURL) VALUES (33,'https://www.example.com/students/international/?utm_source=Adwords&utm_medium=ppc&utm_campaign=IntUGSouth%20Asia&utm_ID=17471&mh_matchtype=b&mh_keyword=online%20llb&mh_adgroupid=133199006627&mh_network=g&gclid=CjwKCAiA4KaRBhBdEiwAZi1zzl8cnNttRgLrtV41vP4ikeqTO-RZ2cgH5AZUPE9498721XpJz7nMrxoCtXQQAvD_BwE');

This my script

Select CASE WHEN charindex('&', substring(CompleteURL,charindex('utm_source',CompleteURL)+1,len(CompleteURL))) > 0 
THEN substring(substring(CompleteURL,charindex('utm_source',CompleteURL)+11,len(CompleteURL)),1, charindex('&', substring(CompleteURL,charindex('utm_source',CompleteURL)+1,len(CompleteURL)))-11 )
ELSE NULL --substring(CompleteURL,charindex('&',CompleteURL)+1,len(CompleteURL)) END AS utm_source,

CASE WHEN charindex('&', substring(CompleteURL,charindex('utm_medium',CompleteURL)+1,len(CompleteURL))) > 0 
THEN substring(substring(CompleteURL,charindex('utm_medium',CompleteURL)+11,len(CompleteURL)),1, charindex('&', substring(CompleteURL,charindex('utm_medium',CompleteURL)+1,len(CompleteURL)))-11 ) 
ELSE NULL --substring(CompleteURL,charindex('&',CompleteURL)+1,len(CompleteURL)) END AS utm_medium,

CASE WHEN charindex('&', substring(CompleteURL,charindex('utm_campaign',CompleteURL)+1,len(CompleteURL))) > 0 
THEN substring(substring(CompleteURL,charindex('utm_campaign',CompleteURL)+13,len(CompleteURL)),1, charindex('&', substring(CompleteURL,charindex('utm_campaign',CompleteURL)+1,len(CompleteURL)))-13 )
ELSE NULL --substring(CompleteURL,charindex('utm_campaign',CompleteURL)+1,len(CompleteURL)) END AS utm_campaign,

CASE WHEN charindex('&', substring(CompleteURL,charindex('utm_id',CompleteURL)+1,len(CompleteURL))) > 0 
THEN substring(substring(CompleteURL,charindex('utm_id',CompleteURL)+7,len(CompleteURL)),1, charindex('&', substring(CompleteURL,charindex('utm_id',CompleteURL)+1,len(CompleteURL)))-7 )
ELSE NULL--substring(CompleteURL,charindex('utm_id',CompleteURL)+1,len(CompleteURL)) END AS utm_id 
from [dbo].[ExampleURLs]

I am getting funny results where if there is no utm tracking it is still populating fields and the utm_id is incorrect in most cases and when I run it on my big table I get the following error but I don't have an example of a URL which is causing the error but is there a way I can add an if error to the cast statement?

Msg 537, Level 16, State 3, Line 3
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.

Any help is appreciated.

I would investigate REGEX parsing for this effort.

Randy
– Randy

2022-06-06 20:30:11 +00:00
Commented Jun 6, 2022 at 20:30 — Randy
– Randy, Commented Jun 6, 2022 at 20:30

John Cappelletti · Accepted Answer · 2022-06-06 21:12:39Z

1

Here is an option using string_split() in concert with a CROSS APPLY

Select A.URL_ID
      ,B.*
 From  [ExampleURLs] A
 Cross Apply (
               Select utm_medium   = max(case when value like 'utm_medium%'   then replace(Value,'utm_medium=','') end )
                     ,utm_source   = max(case when value like 'utm_source%'   then replace(Value,'utm_source=','') end )
                     ,utm_campaign = max(case when value like 'utm_campaign%' then replace(Value,'utm_campaign=','') end )
                     ,utm_id       = max(case when value like 'utm_id%'       then replace(Value,'utm_id=','') end )
                From  string_split( replace(CompleteURL,'?','&'),'&')  
             ) B

edited Jun 6, 2022 at 21:12

answered Jun 6, 2022 at 20:20

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

At's Over a year ago

Thanks for the quick response, I am just running this nowe but lookking good. Thanks for your help

lemon · Accepted Answer · 2022-06-06 20:27:04Z

Your code is correct, except for the missing END keywords inside the CASE statements and commas at the end of each CASE:

SELECT 
    CASE WHEN charindex('&', substring(CompleteURL,charindex('utm_source',CompleteURL)+1,len(CompleteURL))) > 0 
    THEN substring(substring(CompleteURL,charindex('utm_source',CompleteURL)+11,len(CompleteURL)),1, charindex('&', substring(CompleteURL,charindex('utm_source',CompleteURL)+1,len(CompleteURL)))-11 )
    ELSE NULL END,

    CASE WHEN charindex('&', substring(CompleteURL,charindex('utm_medium',CompleteURL)+1,len(CompleteURL))) > 0 
    THEN substring(substring(CompleteURL,charindex('utm_medium',CompleteURL)+11,len(CompleteURL)),1, charindex('&', substring(CompleteURL,charindex('utm_medium',CompleteURL)+1,len(CompleteURL)))-11 ) 
    ELSE NULL END,

    CASE WHEN charindex('&', substring(CompleteURL,charindex('utm_campaign',CompleteURL)+1,len(CompleteURL))) > 0 
    THEN substring(substring(CompleteURL,charindex('utm_campaign',CompleteURL)+13,len(CompleteURL)),1, charindex('&', substring(CompleteURL,charindex('utm_campaign',CompleteURL)+1,len(CompleteURL)))-13 )
    ELSE NULL END,

    CASE WHEN charindex('&', substring(CompleteURL,charindex('utm_id',CompleteURL)+1,len(CompleteURL))) > 0 
    THEN substring(substring(CompleteURL,charindex('utm_id',CompleteURL)+7,len(CompleteURL)),1, charindex('&', substring(CompleteURL,charindex('utm_id',CompleteURL)+1,len(CompleteURL)))-7 )
    ELSE NULL END
FROM [dbo].[ExampleURLs]

Check the demo here.

Eric Castelli · Accepted Answer · 2024-10-02 23:01:53Z

In case anyone is still looking for a solution for this, here's a SQL Server function I built to parse any parameter value from a URL Query string:

create function RetrieveQueryStringParameterValue(@url varchar(2500), @parameterName varchar(255))
RETURNS varchar(255)
AS

BEGIN
    declare @ReturnValue varchar(255) 
    SELECT @ReturnValue = 
    SUBSTRING(s.value, ca.pos + 1, len(s.value))
    FROM STRING_SPLIT(replace(@url,'?','&'), '&') s
    CROSS APPLY (VALUES(CHARINDEX('=', s.value))) ca(pos)
    where ca.pos >= 1 and LEFT(s.value, ca.pos - 1) = @parameterName;

    return @ReturnValue 
end

Calling in SQL:

select dbo.RetrieveQueryStringParameterValue('https://www.sample.com/page/?type=New&utm_campaign=EmailSept24&utm_medium=email','utm_campaign')

Demo here: https://dbfiddle.uk/ljRYltck

Collectives™ on Stack Overflow

Parse utm parameters in sql query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related