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
