Parse utm parameters in sql query-CodePudding

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 of law

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 conversion course&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 of law&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 school uk&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 Asia&utm_ID=17471&mh_matchtype=b&mh_keyword=online llb&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.

CodePudding user response：

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,'?','&'),'&')  B1
             ) B

CodePudding user response：

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.