使用T-SQL解析URL参数

Question

我是一名有用的助手，可以为您翻译文本。

我有一些数字营销的URL。这些URL具有参数，因此我想解析URL并插入一个表格，以便跟踪活动并进行分析。

例如：

https://www.example.com/?utm_source=google&utm_medium=blabla&utm_campaign=gameuser&utm_term=winwin&utm_content=takego

我想插入像那样的表格。我该怎么做？

- Dekareş

---

我使用 MS SQL 2012，但没有 split_part 函数。 - Dekareş

你能创建一个 split() 函数吗？ - Thomas G

你真的，真的不想在TSQL中这样做。最好使用编程语言，并将已解析的值发送到数据库。 - Zohar Peled

Answer 1

你可以使用一个函数。

CREATE FUNCTION [dbo].[ufn_ParseQueryString] ( @QueryString AS VARCHAR(MAX) )  RETURNS @QueryStringTable TABLE ( [Key] VARCHAR(100), [Value] VARCHAR(1000) ) AS BEGIN
DECLARE @QueryStringPair        VARCHAR(2000)
DECLARE @Key                    VARCHAR(100)
DECLARE @Value                  VARCHAR(1000)

WHILE LEN(@QueryString) > 0
BEGIN
    SET @QueryStringPair = LEFT ( @QueryString, ISNULL(NULLIF(CHARINDEX('&', @QueryString) - 1, -1), 
                                  LEN(@QueryString)))
    SET @QueryString = SUBSTRING( @QueryString, ISNULL(NULLIF(CHARINDEX('&', @QueryString), 0), 
                                  LEN(@QueryString)) + 1, LEN(@QueryString))

    SET @Key   = LEFT (@QueryStringPair, ISNULL(NULLIF(CHARINDEX('=', @QueryStringPair) - 1, -1), 
                       LEN(@QueryStringPair)))
    SET @Value = SUBSTRING( @QueryStringPair, ISNULL(NULLIF(CHARINDEX('=', @QueryStringPair), 0), 
                            LEN(@QueryStringPair)) + 1, LEN(@QueryStringPair))

    INSERT INTO @QueryStringTable ( [Key], [Value] )
    VALUES ( @Key, @Value )
END

RETURN

结束

以及测试

SELECT * FROM [dbo].[ufn_ParseQueryString] ( 'pubid=mdfbgd&utm_source=facebook&utm_medium=digimun&placement=Instagram_Stories' )

- r-magalhaes

Answer 2

理想情况下，您应该将此操作放在存储过程中，以便将工作分解为易于处理的块。您可以在一个SQL语句中完成所有操作，但当您到达最后几个参数时，这会变得有点麻烦！基本方法是使用CHARINDEX和SUBSTRING的组合来解析URL...您找到第一个?然后从那里取到第一个&（然后在=两侧拆分它）。然后您找到下一个&（由=拆分），并反复执行此操作。我已经在这里开始了这个过程 - 您应该能够扩展它以从URL获取其余参数 - 它有点慢且笨重，但至少您可以看到正在发生什么：运行这个SQL片段，您很快就会明白。

declare @str VARCHAR(8000)= 'https://www.example.com/?utm_source=google&utm_medium=blabla&utm_campaign=gameuser&utm_term=winwin&utm_content=takego'

DECLARE @str1 VARCHAR(8000)= SUBSTRING(@str, CHARINDEX( '?',@str, 1)+1, CHARINDEX( '&',@str, 1) -CHARINDEX( '?',@str, 1)-1)

SELECT SUBSTRING(@str1, 1, CHARINDEX( '=',@str1, 1)-1)

SELECT SUBSTRING(@str1, CHARINDEX( '=',@str1, 1)+1, LEN(@Str1))

DECLARE @str2  VARCHAR(8000) = SUBSTRING(@str, CHARINDEX( '?',@str, 1) + len(@str1) + 2, LEN(@str))

SELECT @str2 = SUBSTRING(@str2, 1, CHARINDEX( '&',@str2, 1)-1)

SELECT @str2

- john McTighe

---

谢谢John的回答。是的，我想用存储过程来实现这个功能。因为我有一个表格，里面有很多类似的URL。每天都会插入新的URL。我该如何使用存储过程自动完成这个任务？ - Dekareş

要使这种方法适用于表格，需要使用某种循环。如果您的表格大小相对较小且增长缓慢，则可能不是一个大问题。因此，我建议运行某种while循环，并解析所需的列，然后在循环中逐行插入它们，直到全部完成。 - john McTighe