Post reply

find the search string from the url using sql string functions

  • August 1, 2010 at 2:01 pm

    #221983

    Hi i am trying to find the search terms from the url which we enter in the search engine

    for example when i type sql server central in google the url is :

    hl=en&source=hp&aq=f&q=sql+server+central& aqi=&aql=&oq=&gs_rfai=CNJ23UTtUTNGEEYuEhQT8-rGmAQAAAKoEBU_Q7u6a

    i want to pick up the words sql+server+central from this url. I have this case statement in the proc

    which does this but it is picking up wrong words 'f' from q=f instead of 'sql+server+central' from q=sql+server+central.

    this is the case statement. The searchparam value comes from a table. Here it is q

    Referrerparam is the url mentioned above and its stored in a table.

    declare @searchparam varchar(255)

    select @searchparam ='%q=%'

    SELECT ReferrerURL, ReferrerParam, param =

    CASE

    WHEN SUBSTRING(ReferrerParam, 1, 1) = @SearchParam THEN SUBSTRING(ReferrerParam, LEN(@SearchParam)+2, CHARINDEX('&', ReferrerParam))

    ELSE

    CASE

    WHEN patindex('%&%', RIGHT(ReferrerParam, LEN(ReferrerParam)-(PATINDEX(@SearchParam, ReferrerParam)+(LEN(@SearchParam)-3) ))) > 0

    THEN LEFT(RIGHT(ReferrerParam, LEN(ReferrerParam)-(PATINDEX(@SearchParam, ReferrerParam)+(LEN(@SearchParam)-3))), PATINDEX('%&%', RIGHT(ReferrerParam, LEN(ReferrerParam)-(PATINDEX(@SearchParam, ReferrerParam)+(LEN(@SearchParam)-3))))-1)

    ELSE RIGHT(ReferrerParam, LEN(ReferrerParam)-(PATINDEX(@SearchParam, ReferrerParam)+(LEN(@SearchParam)-3))) END

    END,

    Will appreciate your help!!!

  • August 2, 2010 at 9:00 am

    #1201073

    try searching for '%&q=%'

  • August 2, 2010 at 12:54 pm

    #1201188

    declare @searchPhrase varchar(max) = 'hl=en&source=hp&aq=sql+server+central& aqi=&aql=&oq=&gs_rfai=CNJ23UTtUTNGEEYuEhQT8-rGmAQAAAKoEBU_Q7u6a'

    declare @searchParam varchar(255) = '&q='

    select case when CHARINDEX(@searchParam, @searchPhrase) <> 0

    then SUBSTRING(@searchPhrase, CHARINDEX(@searchParam, @searchPhrase) + 3, CHARINDEX('&', @searchPhrase, CHARINDEX(@searchParam, @searchPhrase) + 3) - (CHARINDEX(@searchParam, @searchPhrase) + 3)) end

    └> bt


    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply