Wednesday, May 5, 2010

Parsing Search Terms from URL in SQL Server

I often have the save the referrer URL for sites I work on to see where the visitors are coming from.
I needed a quick way to figure out what were people searching when they came from a search engine.
Most search engines will have the search term in the query string as q=search+term it could be in the middle, end or beginning of the query string.
I created a quick UDF to parse these and return the search term:

CREATE FUNCTION fnGetSearchTerms (@MyUrl nvarchar(1000))
RETURNS VARCHAR(500)
AS
BEGIN

DECLARE @idx int --Index of the search term
DECLARE @iex int --Index of the seperator after the search term

SET @idx =1

--Return value
DECLARE @Ret varchar(500)

WHILE ((SUBSTRING(@MyUrl,@idx,3)!='&q=') AND (SUBSTRING(@MyUrl,@idx,3)!='?q=') ) AND @idx < LEN(@MyUrl)
BEGIN
SET @idx = @idx+1
END

IF ((SUBSTRING(@MyUrl,@idx,3)='&q=') OR (SUBSTRING(@MyUrl,@idx,3)='?q=') )
BEGIN

SET @idx=@idx+3
SET @iex=Charindex('&',@MyUrl,@idx+3)
if @iex=0
BEGIN
SET @iex = LEN(@MyUrl)+1
END

SET @Ret=SUBSTRING(@MyUrl, @idx, @iex-@idx)

END
SET @Ret=REPLACE(@Ret,'+',' ')

RETURN @Ret

END
go

Quick and simple



1 comment: