How to extract URL querystring parameters in SQL Server without writing a function?

Question

I'm having trouble identifying all the querystring parameters that are used on a site. I want to write a T-SQL query that extracts all parameters and counts them, but I don't have permission to write SQL functions, so this solution isn't much help.

The field that I'm working with (Query) includes data that looks like this:

_=1457999955221
tab=profile
tab=tags&sort=votes&page=13
page=5&sort=newest&pagesize=15
...

The query I need to write would return the result:

querystring | count
___________________
_           |  1
tab         |  2
sort        |  2
page        |  2
pagesize    |  1
...

Any help is greatly appreciated.

Apparently you have access to SQL Management Studio to run queries? You can write T-SQL and parse it yourself in a loop. — user5684647
– user5684647, Commented Mar 24, 2016 at 20:12
There's nothing particularly special about the function you linked that says it has to be a function. You could easily strip out the logic of the function and execute it as a normal query. — Bacon Bits
– Bacon Bits, Commented Mar 24, 2016 at 20:31
@BaconBits Though that partcular function uses a recursive CTE. It would be easy to cross apply the function to a table but not to declare a recursive cte inside an apply. — Martin Smith
– Martin Smith, Commented Mar 24, 2016 at 21:03

Martin Smith · Accepted Answer · 2016-03-27 14:11:22Z

6

+50

You can borrow one of the functions from here and just inline it into the query.

An example below. I would not expect good performance. Creating a CLR function is by far the most efficient way of splitting strings prior to SQL Server 2016.

DECLARE @QueryStrings Table
(
Query VARCHAR(8000)
)

INSERT INTO @QueryStrings 
VALUES
('INVALID'),
('_=1457999955221'),
('tab=profile'),
('tab=tags&sort=votes&page=13'),
('page=5&sort=newest&pagesize=15');


WITH E1(N)        AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                        UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                        UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
    E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
    E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
    E42(N)       AS (SELECT 1 FROM E4 a, E2 b)
    SELECT parameter, count(*)
    FROM   @QueryStrings qs
        CROSS APPLY (SELECT SUBSTRING(qs.Query, t.N + 1, ISNULL(NULLIF(CHARINDEX('&', qs.Query, t.N + 1), 0) - t.N - 1, 8000))
                    FROM   (SELECT 0
                            UNION ALL
                            SELECT TOP (DATALENGTH(ISNULL(qs.Query, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                            FROM   E42) t(N)
                    WHERE  ( SUBSTRING(qs.Query, t.N, 1) = '&'
                                OR t.N = 0 )) ca1(split_result) 
        CROSS APPLY (SELECT CHARINDEX('=',split_result)) ca2(pos) 
        CROSS APPLY (SELECT CASE WHEN pos > 0 THEN LEFT(split_result,pos-1) END, 
                            CASE WHEN pos > 0 THEN SUBSTRING(split_result, pos+1,8000) END
                        WHERE  pos > 0) ca3(parameter,value) 
    GROUP BY parameter

edited Mar 27, 2016 at 14:11

answered Mar 25, 2016 at 14:44

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

2 Comments

samthebrand Over a year ago

I don't know exactly how this query does it, but it looks like it does what I need it to do. Big thanks.

Greg Bray Over a year ago

Here is a great benchmark on the CLR approach and the new STRING_SPLIT that is available in SQL 2016 sqlperformance.com/2016/03/t-sql-queries/string-split

gofr1 · Accepted Answer · 2016-03-29 15:44:07Z

2

More sexy way to approach that:

DECLARE @xml xml

;WITH cte AS (
SELECT *
FROM (VALUES
('_=1457999955221'),
('tab=profile'),
('tab=tags&sort=votes&page=13'),
('page=5&sort=newest&pagesize=15')
) as T(Query))

SELECT  @xml = (
SELECT CAST(
(
SELECT '<d><param>' + REPLACE(REPLACE((STUFF((
SELECT '/' + REPLACE(REPLACE(Query,'&','/'),'=','!')
FROM cte
FOR XML PATH('')
),1,1,'')),'/','</value><param>'),'!','</param><value>') + '</value></d>') as xml))

;WITH final AS (
SELECT t.v.value('.','nvarchar(20)') as querystring
FROM @xml.nodes('/d/param') as t(v)
)

SELECT querystring, COUNT(*) as [count]
FROM final
GROUP BY querystring

Result:

querystring          count
-------------------- -----------
_                    1
page                 2
pagesize             1
sort                 2
tab                  2

(5 row(s) affected)

edited Mar 29, 2016 at 15:44

answered Mar 29, 2016 at 13:46

gofr1

16k11 gold badges47 silver badges56 bronze badges

2 Comments

samthebrand Over a year ago

I think this is close, but it looks like it counts the querystring values in addition to the parameters.

gofr1 Over a year ago

I saw you have 'tags' in your output, so I thought you need all. :) I'll change my answer later :)

Stefan Steiger · Accepted Answer · 2020-05-15 15:06:27Z

Necromancing.
This can now be done easily with SQL Server 2016+ (13.x+)

-- Required for STRING_SPLIT: 
-- ALTER DATABASE <db_name> SET COMPATIBILITY_LEVEL = 130 -- >= 130


BEGIN TRY 
    DECLARE @sql nvarchar(MAX); 
    -- https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15
    -- SET @sql = N'ALTER DATABASE [COR_Basic_Demo_V4] SET COMPATIBILITY_LEVEL = 130; '; 
    SET @sql = N'ALTER DATABASE ' + QUOTENAME(DB_NAME()) + N' SET COMPATIBILITY_LEVEL = ' + (SELECT CAST(MAX(compatibility_level) AS nvarchar(10)) FROM sys.databases) + '; '; 
    -- PRINT @sql; 
    EXECUTE(@sql); 
END TRY 
BEGIN CATCH 
    -- Execute error retrieval routine. 
    -- EXECUTE usp_GetErrorInfo; 

    SELECT  
         ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  
    ; 
END CATCH  


-- Here comes the actual computation 


DECLARE @input nvarchar(4000)
SET @input = N'_=1457999955221
tab=profile
tab=tags&sort=votes&page=13
page=5&sort=newest&pagesize=15'

;WITH CTE AS 
(
    SELECT 
         value 
        ,SUBSTRING(splitted.value, 1, NULLIF(CHARINDEX('=', splitted.value), 0) -1) AS k
        ,SUBSTRING(splitted.value, NULLIF(CHARINDEX('=', splitted.value), 0) + 1, LEN(splitted.value)) AS v 
    FROM STRING_SPLIT
    (
        REPLACE
        (
             REPLACE(@input, CHAR(13), '')
            ,CHAR(10)
            ,'&'
        )
        , '&'
    ) AS splitted 
)
SELECT 
     k
    ,COUNT(v) AS cnt 
    ,COUNT(DISTINCT v) AS dist_cnt 
FROM CTE 
GROUP BY k

For earlier versions, or if you actually need to decompose a full-url:

DECLARE @String nvarchar(4000) 
DECLARE @path nvarchar(MAX) 
DECLARE @hash nvarchar(MAX) 
DECLARE @Delimiter nchar(1)


SET @String = 'http://localhost:10004/Kamikatze/ajax/AnySelect.ashx?sql=Maps.ObjectBounds.sql&BE_ID=123&obj_uid=fd4ea870-82eb-4c37-bb67-3e8d5b7b7ac2&&in_stichtag=1589528927178&no_cache=1589528927178&no_cache=1589528927178&moo=moo#foobar'
SET @String = 'sql=Maps.ObjectBounds.sql&BE_ID=123&obj_uid=fd4ea870-82eb-4c37-bb67-3e8d5b7b7ac2&&in_stichtag=1589528927178&no_cache=1589528927178&no_cache=1589528927178&moo=moo#foobar'
-- SET @String = 'http://localhost:10004/Kamikatze/ajax/AnySelect.ashx?sql=Maps.ObjectBounds.sql&BE_ID=123&obj_uid=fd4ea870-82eb-4c37-bb67-3e8d5b7b7ac2&&in_stichtag=1589528927178&no_cache=1589528927178&no_cache=1589528927178&moo=moo'
SET @Delimiter = '&'



SELECT 
     @path = SUBSTRING(@String, 1, NULLIF(CHARINDEX('?', @String) - 1, -1)) -- AS path 
    ,@hash = RIGHT(@String, LEN(@String) - NULLIF(CHARINDEX(N'#', @String), 0)  ) -- AS hash 


SELECT -- remove hash
    @String = SUBSTRING
    (
         @String
        ,1
        ,COALESCE(NULLIF(CHARINDEX(N'#', @String), 0) - 1, LEN(@String) )
    ) -- AS xxx 
; 

SELECT -- remove path 
    @String = SUBSTRING
    (   @String
        ,CHARINDEX(N'?', @String) + 1 
        ,100000
    )
;   

;WITH Split(id, stpos, endpos, data) 
AS
(
    SELECT 
         0 AS id 
        ,0 AS stpos 
        ,CHARINDEX(@Delimiter, @String) AS endpos 
        ,SUBSTRING(@String, 0, COALESCE(NULLIF(CHARINDEX(@Delimiter, @String), 0), LEN(@String)+1) ) AS data 

    UNION ALL

    SELECT 
         Split.id + 1 AS id 
        ,Split.endpos + 1 AS stpos 
         ,CHARINDEX(@Delimiter, @String, Split.endpos+1) AS endpos 
         ,SUBSTRING(@String, Split.endpos + 1, COALESCE(NULLIF(CHARINDEX(@Delimiter, @String, Split.endpos+1), 0), LEN(@String)+1) - Split.endpos - 1) AS data  
    FROM Split 
    WHERE endpos > 0
)
SELECT  
     id
    -- ,stpos
    -- ,endpos
    -- ,SUBSTRING(@String, stpos, COALESCE(NULLIF(endpos, 0), LEN(@String)+1) - stpos) AS data_simple 
    ,data
    ,@path AS path 
    ,@hash AS hash 
    ,SUBSTRING(data, 1, NULLIF(charindex('=', data), 0) -1) AS k
    ,SUBSTRING(data, NULLIF(charindex('=', data), 0) + 1, LEN(data)) AS v 
FROM Split

And if you want a function:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tfu_DecomposeUrl]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
EXECUTE('CREATE FUNCTION dbo.tfu_DecomposeUrl( ) RETURNS TABLE AS RETURN ( SELECT 123 AS abc) '); 
GO





ALTER FUNCTION dbo.tfu_DecomposeUrl 
(
    @input_string NVARCHAR(4000) 
)
RETURNS TABLE
AS
RETURN
(

    WITH CTE AS 
    (
        SELECT 
             SUBSTRING(@input_string, 1, NULLIF(CHARINDEX('?', @input_string) - 1, -1)) AS query_path 
            ,RIGHT(@input_string, LEN(@input_string) - NULLIF(CHARINDEX(N'#', @input_string), 0)  ) AS query_hash 
            ,SUBSTRING
            (
                 @input_string
                ,1
                ,COALESCE(NULLIF(CHARINDEX(N'#', @input_string), 0) - 1, LEN(@input_string) )
            ) AS PathWithoutHash 
    )
    ,CTE2 AS 
    (
        SELECT 
             CTE.query_path 
            ,CTE.query_hash 
            ,SUBSTRING
            (    PathWithoutHash
                ,CHARINDEX(N'?', PathWithoutHash) + 1 
                ,100000
            ) AS KeyValueString  
        FROM CTE 
    )
    ,Split(id, stpos, endpos, data, query_path, query_hash) 
    AS
    (
        SELECT 
             0 AS id 
            ,0 AS stpos 
            ,CHARINDEX(N'&', CTE2.KeyValueString) AS endpos 
            ,SUBSTRING(CTE2.KeyValueString, 0, COALESCE(NULLIF(CHARINDEX(N'&', CTE2.KeyValueString), 0), LEN(CTE2.KeyValueString)+1) ) AS data 
            ,CTE2.query_path 
            ,CTE2.query_hash 
        FROM CTE2 

        UNION ALL

        SELECT 
             Split.id + 1 AS id 
            ,Split.endpos + 1 AS stpos 
            ,CHARINDEX(N'&', CTE2.KeyValueString, Split.endpos+1) AS endpos 
            ,SUBSTRING(CTE2.KeyValueString, Split.endpos + 1, COALESCE(NULLIF(CHARINDEX(N'&', CTE2.KeyValueString, Split.endpos+1), 0), LEN(CTE2.KeyValueString)+1) - Split.endpos - 1) AS data  
            ,CTE2.query_path 
            ,CTE2.query_hash 
        FROM Split 
        CROSS JOIN CTE2 
        WHERE endpos > 0
    )
    SELECT 
         id
         -- ,stpos
         -- ,endpos
         -- ,SUBSTRING(@String, stpos, COALESCE(NULLIF(endpos, 0), LEN(@String)+1) - stpos) AS data_simple 
        ,data
        ,query_path 
        ,query_hash 
        ,SUBSTRING(data, 1, NULLIF(CHARINDEX('=', data), 0) -1) AS k
        ,SUBSTRING(data, NULLIF(CHARINDEX('=', data), 0) + 1, LEN(data)) AS v 
    FROM Split 
)


GO

Which can be simplified into this

DECLARE @input_string nvarchar(4000) 


SET @input_string = 'http://localhost:10004/Kamikatze/ajax/AnySelect.ashx?sql=Maps.ObjectBounds.sql&BE_ID=123&obj_uid=fd4ea870-82eb-4c37-bb67-3e8d5b7b7ac2&&in_stichtag=1589528927178&no_cache=1589528927178&no_cache=1589528927178&moo=moo#foobar'
-- SET @input_string = 'sql=Maps.ObjectBounds.sql&BE_ID=123&obj_uid=fd4ea870-82eb-4c37-bb67-3e8d5b7b7ac2&&in_stichtag=1589528927178&no_cache=1589528927178&no_cache=1589528927178&moo=moo#foobar'
-- SET @input_string = 'http://localhost:10004/Kamikatze/ajax/AnySelect.ashx?sql=Maps.ObjectBounds.sql&BE_ID=123&obj_uid=fd4ea870-82eb-4c37-bb67-3e8d5b7b7ac2&&in_stichtag=1589528927178&no_cache=1589528927178&no_cache=1589528927178&moo=moo'
-- SET @input_string = 'sql=Maps.ObjectBounds.sql&BE_ID=123&obj_uid=fd4ea870-82eb-4c37-bb67-3e8d5b7b7ac2&&in_stichtag=1589528927178&no_cache=1589528927178&no_cache=1589528927178&moo=moo'




;WITH CTE AS 
(
    SELECT 
         SUBSTRING(@input_string, 1, NULLIF(CHARINDEX('?', @input_string) - 1, -1)) AS query_path 
        ,RIGHT(@input_string, LEN(@input_string) - NULLIF(CHARINDEX(N'#', @input_string), 0)  ) AS query_hash 
        ,SUBSTRING
        (
             @input_string
            ,1
            ,COALESCE(NULLIF(CHARINDEX(N'#', @input_string), 0) - 1, LEN(@input_string) )
        ) AS PathWithoutHash 
)
,CTE2 AS 
(
    SELECT 
         CTE.query_path 
        ,CTE.query_hash 
        ,SUBSTRING
        (    PathWithoutHash
            ,CHARINDEX(N'?', PathWithoutHash) + 1 
            ,100000
        ) AS KeyValueString  
    FROM CTE 
)
SELECT 
     t.id 
    ,t.data
    ,CTE2.query_path 
    ,CTE2.query_hash 
    ,SUBSTRING(t.data, 1, NULLIF(CHARINDEX('=', t.data), 0) -1) AS k
    ,SUBSTRING(t.data, NULLIF(CHARINDEX('=', t.data), 0) + 1, LEN(t.data)) AS v 
FROM CTE2 
OUTER APPLY dbo.tfu_FastSplitString(CTE2.KeyValueString, N'&') AS t

And a table-valued string-splitting function:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.tfu_FastSplitString') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
EXECUTE('CREATE FUNCTION dbo.tfu_FastSplitString( ) RETURNS TABLE AS RETURN ( SELECT 123 AS abc) '); 
GO




ALTER FUNCTION dbo.tfu_FastSplitString 
(
     @input_string nvarchar(4000) 
    ,@delimiter nchar(1) 
)
RETURNS TABLE
AS
RETURN
( 
    WITH Split(id, stpos, endpos) -- , data) 
    AS
    (
        SELECT 
             0 AS id 
            ,0 AS stpos 
            ,CHARINDEX(@delimiter, @input_string) AS endpos 
            -- ,SUBSTRING(@input_string, 0, COALESCE(NULLIF(CHARINDEX(@delimiter, @input_string), 0), LEN(@input_string)+1) ) AS data 

        UNION ALL

        SELECT 
             Split.id + 1 AS id 
            ,Split.endpos + 1 AS stpos 
            ,CHARINDEX(@delimiter, @input_string, Split.endpos+1) AS endpos 
            -- ,SUBSTRING(@input_string, Split.endpos + 1, COALESCE(NULLIF(CHARINDEX(@delimiter, @input_string, Split.endpos+1), 0), LEN(@input_string)+1) - Split.endpos - 1) AS data  
        FROM Split 
        WHERE endpos > 0
    )
    SELECT  
         id
        -- ,stpos
        -- ,endpos
        -- ,data 
        ,SUBSTRING(@input_string, stpos, COALESCE(NULLIF(endpos, 0), LEN(@input_string)+1) - stpos) AS data 
    FROM Split  
)


GO

These are inline table-valued functions, so they should be fast.
If it were a multi-statement table-valued function, it would be slow.

Collectives™ on Stack Overflow

How to extract URL querystring parameters in SQL Server without writing a function?

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related