在sql查詢中決議utm引數-有解無憂

我的資料庫中有一個表來跟蹤訪問者的頁面瀏覽量，我正在嘗試從 url 中提取 utm 跟蹤引數

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

您可以使用此腳本創建表

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');

這是我的腳本

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]

我得到有趣的結果，如果沒有 utm 跟蹤，它仍在填充欄位，并且 utm_id 在大多數情況下不正確，當我在我的大表上運行它時，我收到以下錯誤，但我沒有 URL 示例這是導致錯誤的原因，但有沒有辦法可以在強制轉換陳述句中添加 if 錯誤？

訊息 537，級別 16，狀態 3，行 3
傳遞給 LEFT 或 SUBSTRING 函式的長度引數無效。
該陳述句已終止。

任何幫助表示贊賞。

uj5u.com熱心網友回復：

這是一個與 a 一起使用的string_split()選項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

uj5u.com熱心網友回復：

您的代碼是正確的，除了陳述句中缺少的END關鍵字CASE和每個末尾的逗號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]

在此處查看演示。

轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/486961.html

標籤：sql sql服务器

上一篇：如何訪問oracle中listagg中的元素？

下一篇：SQL：只有mm-dd的日期型別列