使用T-SQL解析URL参数

Question

我有一些数字营销的网址。这些URL都有参数，所以我想解析URL并插入一个表。用于跟踪活动和分析。

例如：

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

​

​

我想要像这样插入表格。我该怎么做呢？

Answer 1

理想情况下，您将在存储过程中执行此操作，以便可以将工作分解为小块。您可以在一条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

Answer 2

您可以使用函数

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