Friday, October 3, 2008

SQL Stored Procedure - GetQueryStringValue

Recently, I had a requirement to create a SQL function to get the Query String value from a given Query String for a particular Query String Key. (e.g) Input QueryString = 'search=google&client=safari&data=xyz'
Input QS Key = 'client'
Output = 'safari'
So I came up with the following SQL function.





CREATE FUNCTION [dbo].[GetQueryStringValue]
(
@inputQS VARCHAR(400),
@QSKey VARCHAR(50)
)
RETURNS VARCHAR(100)
AS
BEGIN

DECLARE @positionStart INT
DECLARE @positionEnd INT


SET @positionStart = PATINDEX('%' + @qsKey + '%', @inputQS) 
IF @positionStart > 0
BEGIN
SET @positionStart = @positionStart + len(@qsKey) + 1 -- Get QSKey pos

SET @positionEnd = CHARINDEX('&', @inputQS,  @positionStart) -- Get the pos of next '&'
RETURN SUBSTRING(@inputQS, @positionStart, @positionEnd - @positionStart)
END
ELSE
RETURN ''
END


--------------------------
Usage : select dbo. GetQueryStringValue('search=google&client=safari&data=xyz', 'client')
output : safari

No comments:

Post a Comment