Parsing QueryString (GET) Variables From a URL using T-SQL

23May/090

Parsing QueryString (GET) Variables From a URL using T-SQL

First of all - I know, don't do this. The application that put the URL in the column in the first place is MUCH better at handling URLs and ideally, you would just add columns for the GET variables you're after and have the application put those in. Parsing them out after the fact from a VARCHAR field is insane.

So now we're here and we need to do that thing that we said we shouldn't do. My approach is to use a Table Valued Function that only returns one column - the value of the variable or NULL if it can't find the variable in the querystring.

Example

SELECT
	a.[Id],
	b.[GetVariableValue] AS [Foo],
	c.[GetVariableValue] AS [Bar]
FROM
	[dbo].[SourceTable] a
	CROSS APPLY [dbo].[Utility_UrlParseGetVariable_F01]([URL], 'foo', 0) b
	CROSS APPLY [dbo].[Utility_UrlParseGetVariable_F01]([URL], 'bar', 0) c

Table Valued Function

/*************************************************************************************
***
*** Procedure:  		[Utility_UrlParseGetVariable_F01]
*** Purpose:		    Attempts to parse a given GET variable out of a URL string
***				
***			
*** Author:		      tl
*** Date Created:	  2009-05-22
*** 
*** Revision History
*** Date		Author			Description
*** 2009-05-22	tl				Created
*************************************************************************************/
ALTER FUNCTION [dbo].[Utility_UrlParseGetVariable_F01] 
(
	@Url VARCHAR(2048),
	@VariableName VARCHAR(255)
)
RETURNS @GetVariable TABLE ([GetVariableValue] VARCHAR(255))
AS
BEGIN
 
	DECLARE @SUBSTRING VARCHAR(2048)
	DECLARE @variableValue VARCHAR(255)
 
	SET @VariableName = @VariableName + '='
 
	-- Case Insensitive - munge both inputs
	SET @VariableName = LOWER(@VariableName)
	SET @Url = LOWER(@Url)
 
	IF (CHARINDEX(@VariableName,@Url) > 0)
	BEGIN
		SET @SUBSTRING = RIGHT(@Url, LEN(@Url) - CHARINDEX(@VariableName,@Url) - LEN(@VariableName) + 1)
		IF (CHARINDEX('&', @SUBSTRING) > 0)
			SET @variableValue = CAST(LEFT(@SUBSTRING, CHARINDEX('&', @SUBSTRING) - 1) AS VARCHAR(255))
		ELSE
			SET @variableValue = CAST(@SUBSTRING AS VARCHAR(255))
 
		INSERT INTO @GetVariable ([GetVariableValue]) VALUES (@variableValue)
	END
	ELSE
		INSERT INTO @GetVariable ([GetVariableValue]) VALUES (NULL)
 
	RETURN
END

Issues

The current TVF munges everything to lower case - the return is lower case as well. This works fine for me as I was parsing out INT values anyway (so I use ISNULL(...,0) inside a cast) for the actual value.
The way it's written, if you look for the variable 'bar' and the querystring actually has a variable called 'foobar' before it - this function will take the value of 'foobar' and return it (because it's just doing a CHARINDEX for 'bar=' in the querystring

Why not RegEx via CLR?
Because one of the DBAs asked me not to RegEx via CLR is a cleaner and more accurate way to do it. Better yet, you could use CLR and create a Uri object, then use HttpUtility.ParseQueryString(uri.Query).Name('bar') to get the appropriate value.

Tagged as: sql, string parsing, t-sql, table valued function Leave a comment

Comments (0) Trackbacks (0) ( subscribe to comments on this post )

No comments yet.

No trackbacks yet.

« Maintaining a Type 1 Slowly Changing Dimension (SCD) using T-SQL A Cleaner Way to Detect the State of a SQL Server Agent Job In SQL Server 2005 »

Social Networks

What I'm Doing...

@scaleovenstove @jbollinger let's do it. "BItter" ;-) in reply to scaleovenstove 3 weeks ago
@joekieffer good luck! in reply to joekieffer 2010-07-10
@ben_p bah, i'm proud of him - that's a ridiculous name to have on a resume. give him at least 2 years before you start the peer pressure in reply to ben_p 2010-06-11
@michaelfransen ha, I was pretty sure u were ignoring me ;-) ty. in reply to michaelfransen 2010-06-11
Last day at W3i today - moving to Trek Bicycle Corp! 2010-06-11
More updates...

tim laqua dot com Thoughts and Code from Tim Laqua