/*
Created by Chrissy LeMaire (clemaire@gmail.com)
Website: http://netnerds.net/

This C# UDF does the following
1. Guesses the search engine's search keyword delimiter
2. Finds the search term and extracts it
3. Replaces querystring percentage encoding with its human readable equiv.

NO WARRANTIES, USE THIS AT YOUR OWN RISK, etc.
*/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString QSCleaner(string strURL)
    {
        int ampStart, slashStart, delimiterStart;
        string strQueryString = "";
        string strDelimiter = "";

        strURL = strURL.Replace("aq=", "aa=");
        strURL = strURL.Replace("as_q=", "aa=");
        strURL = strURL.Replace("as_oq=", "aa=");
        strURL = strURL.Replace("as_eq=", "aa=");

        while (strDelimiter.Length == 0)
        {

    // most used search delimiters
         if (strURL.IndexOf("q=") > 0)
        { strDelimiter = "q="; break; }

         if (strURL.IndexOf("p=") > 0)
        { strDelimiter = "p="; break; }

         if (strURL.IndexOf("query=") > 0)
        { strDelimiter = "="; break; }

         if (strURL.IndexOf("search=") > 0)
        { strDelimiter = "search="; break; }

         if (strURL.IndexOf("qry=") > 0)
        { strDelimiter = "qry="; break; }

         if (strURL.IndexOf("p=") > 0)
        { strDelimiter = "p="; break; }

         if (strURL.IndexOf("keywords=") > 0)
        { strDelimiter = "keywords="; break; }

         if (strURL.IndexOf("keyword=") > 0)
        { strDelimiter = "keyword="; break; }

         if (strURL.IndexOf("Keywords=") > 0)
        { strDelimiter = "Keywords="; break; }

         if (strURL.IndexOf("MT=") > 0)
        { strDelimiter = "MT="; break; }

         if (strURL.IndexOf("s=") > 0)
        { strDelimiter = "s="; break; }

         if (strURL.IndexOf("key=") > 0)
        { strDelimiter = "key="; break; }
        // end most used

         if (strURL.IndexOf("B1=") > 0)
        { strDelimiter = "B1="; break; }

         if (strURL.IndexOf("Claus=") > 0)
        { strDelimiter = "Claus="; break; }

         if (strURL.IndexOf("DTqb1=") > 0)
        { strDelimiter = "DTqb1="; break; }

         if (strURL.IndexOf("KERESES=") > 0)
        { strDelimiter = "KERESES="; break; }

         if (strURL.IndexOf("KEY=") > 0)
        { strDelimiter = "KEY="; break; }

         if (strURL.IndexOf("KeyWords=") > 0)
        { strDelimiter = "KeyWords="; break; }

         if (strURL.IndexOf("PA=") > 0)
        { strDelimiter = "PA="; break; }

         if (strURL.IndexOf("Q1=") > 0)
        { strDelimiter = "Q1="; break; }

         if (strURL.IndexOf("QRY=") > 0)
        { strDelimiter = "QRY="; break; }

         if (strURL.IndexOf("QUERY=") > 0)
        { strDelimiter = "QUERY="; break; }

         if (strURL.IndexOf("Query=") > 0)
        { strDelimiter = "Query="; break; }

         if (strURL.IndexOf("SEARCHSTRING=") > 0)
        { strDelimiter = "SEARCHSTRING="; break; }

         if (strURL.IndexOf("SP=") > 0)
        { strDelimiter = "SP="; break; }

         if (strURL.IndexOf("SearchString=") > 0)
        { strDelimiter = "SearchString="; break; }

         if (strURL.IndexOf("SearchText=") > 0)
        { strDelimiter = "SearchText="; break; }

         if (strURL.IndexOf("T=") > 0)
        { strDelimiter = "T="; break; }

         if (strURL.IndexOf("Terms=") > 0)
        { strDelimiter = "Terms="; break; }

         if (strURL.IndexOf("Text=") > 0)
        { strDelimiter = "Text="; break; }

         if (strURL.IndexOf("Words=") > 0)
        { strDelimiter = "Words="; break; }

         if (strURL.IndexOf("ara=") > 0)
        { strDelimiter = "ara="; break; }

         if (strURL.IndexOf("as_q=") > 0)
        { strDelimiter = "as_q="; break; }

         if (strURL.IndexOf("ask=") > 0)
        { strDelimiter = "ask="; break; }

         if (strURL.IndexOf("begriff=") > 0)
        { strDelimiter = "begriff="; break; }

         if (strURL.IndexOf("cadena=") > 0)
        { strDelimiter = "cadena="; break; }

         if (strURL.IndexOf("eingabe=") > 0)
        { strDelimiter = "eingabe="; break; }

         if (strURL.IndexOf("general=") > 0)
        { strDelimiter = "general="; break; }

         if (strURL.IndexOf("in=") > 0)
        { strDelimiter = "in="; break; }

         if (strURL.IndexOf("item=") > 0)
        { strDelimiter = "item="; break; }

         if (strURL.IndexOf("jump=") > 0)
        { strDelimiter = "jump="; break; }

         if (strURL.IndexOf("k=") > 0)
        { strDelimiter = "k="; break; }

         if (strURL.IndexOf("key=") > 0)
        { strDelimiter = "key="; break; }

         if (strURL.IndexOf("kw=") > 0)
        { strDelimiter = "kw="; break; }

         if (strURL.IndexOf("look_for=") > 0)
        { strDelimiter = "look_for="; break; }

         if (strURL.IndexOf("oldquery=") > 0)
        { strDelimiter = "oldquery="; break; }

         if (strURL.IndexOf("palabras=") > 0)
        { strDelimiter = "palabras="; break; }

         if (strURL.IndexOf("palavra=") > 0)
        { strDelimiter = "palavra="; break; }

         if (strURL.IndexOf("q%3D") > 0)
        { strDelimiter = "q%3D"; break; }

         if (strURL.IndexOf("q1=") > 0)
        { strDelimiter = "q1="; break; }

         if (strURL.IndexOf("qkw=") > 0)
        { strDelimiter = "qkw="; break; }

         if (strURL.IndexOf("qr=") > 0)
        { strDelimiter = "qr="; break; }

         if (strURL.IndexOf("qry=") > 0)
        { strDelimiter = "qry="; break; }

         if (strURL.IndexOf("qs=") > 0)
        { strDelimiter = "qs="; break; }

         if (strURL.IndexOf("qt=") > 0)
        { strDelimiter = "qt="; break; }

         if (strURL.IndexOf("qu=") > 0)
        { strDelimiter = "qu="; break; }

         if (strURL.IndexOf("queryText=") > 0)
        { strDelimiter = "queryText="; break; }

         if (strURL.IndexOf("query_1=") > 0)
        { strDelimiter = "query_1="; break; }

         if (strURL.IndexOf("queryterm=") > 0)
        { strDelimiter = "queryterm="; break; }

         if (strURL.IndexOf("question=") > 0)
        { strDelimiter = "question="; break; }

         if (strURL.IndexOf("qw=") > 0)
        { strDelimiter = "qw="; break; }

         if (strURL.IndexOf("r=") > 0)
        { strDelimiter = "r="; break; }

         if (strURL.IndexOf("request=") > 0)
        { strDelimiter = "request="; break; }

         if (strURL.IndexOf("sTerm=") > 0)
        { strDelimiter = "sTerm="; break; }

         if (strURL.IndexOf("sc=") > 0)
        { strDelimiter = "sc="; break; }

         if (strURL.IndexOf("searchString=") > 0)
        { strDelimiter = "searchString="; break; }

         if (strURL.IndexOf("searchWord=") > 0)
        { strDelimiter = "searchWord="; break; }

         if (strURL.IndexOf("search_forquery=") > 0)
        { strDelimiter = "search_forquery="; break; }

         if (strURL.IndexOf("search_string=") > 0)
        { strDelimiter = "search_string="; break; }

         if (strURL.IndexOf("search_term=") > 0)
        { strDelimiter = "search_term="; break; }

         if (strURL.IndexOf("searchfor=") > 0)
        { strDelimiter = "searchfor="; break; }

         if (strURL.IndexOf("searchquery=") > 0)
        { strDelimiter = "="; break; }

         if (strURL.IndexOf("searchtext=") > 0)
        { strDelimiter = "searchquery="; break; }

         if (strURL.IndexOf("soegeord=") > 0)
        { strDelimiter = "soegeord="; break; }

         if (strURL.IndexOf("srch=") > 0)
        { strDelimiter = "srch="; break; }

         if (strURL.IndexOf("str=") > 0)
        { strDelimiter = "str="; break; }

         if (strURL.IndexOf("string=") > 0)
        { strDelimiter = "string="; break; }

         if (strURL.IndexOf("su=") > 0)
        { strDelimiter = "su="; break; }

         if (strURL.IndexOf("suche=") > 0)
        { strDelimiter = "suche="; break; }

         if (strURL.IndexOf("suchstr=") > 0)
        { strDelimiter = "suchstr="; break; }

         if (strURL.IndexOf("szukaj=") > 0)
        { strDelimiter = "szukaj="; break; }

         if (strURL.IndexOf("t=") > 0)
        { strDelimiter = "t="; break; }

         if (strURL.IndexOf("term=") > 0)
        { strDelimiter = "term="; break; }

         if (strURL.IndexOf("terml=") > 0)
        { strDelimiter = "terml="; break; }

         if (strURL.IndexOf("terms=") > 0)
        { strDelimiter = "terms="; break; }

         if (strURL.IndexOf("text=") > 0)
        { strDelimiter = "text="; break; }

         if (strURL.IndexOf("u=") > 0)
        { strDelimiter = "u="; break; }

         if (strURL.IndexOf("w=") > 0)
        { strDelimiter = "w="; break; }

         if (strURL.IndexOf("web/") > 0)
        { strDelimiter = "web/"; break; }

         if (strURL.IndexOf("what=") > 0)
        { strDelimiter = "what="; break; }

         if (strURL.IndexOf("word=") > 0)
        { strDelimiter = "word="; break; }

         if (strURL.IndexOf("words=") > 0)
        { strDelimiter = "words="; break; }

         if (strURL.IndexOf("x=") > 0)
        { strDelimiter = "x="; break; }

            //if by this time it isnt set.. it's not likely a search string
            if (strDelimiter.Length == 0) strDelimiter = "unknown";
        }

        if (strURL.IndexOf(strDelimiter) > 0)
        {
            try
            {
                delimiterStart = strURL.IndexOf(strDelimiter) + strDelimiter.Length;
                strQueryString = strURL.Substring(delimiterStart, (strURL.Length - delimiterStart));
                ampStart = strQueryString.IndexOf("&");
                if (ampStart > 0) strQueryString = strQueryString.Substring(0, ampStart);

                slashStart = strQueryString.IndexOf("/");
                if (slashStart > 0) strQueryString = strQueryString.Substring(0, slashStart - 1);

                // because I can't get System.Web.HttpUtility.UrlDecode to work
                strQueryString = strQueryString.Replace("%20", " ");
                strQueryString = strQueryString.Replace("%21", "!");
                strQueryString = strQueryString.Replace("%22", "\"");
                strQueryString = strQueryString.Replace("%23", "#");
                strQueryString = strQueryString.Replace("%24", "$");
                strQueryString = strQueryString.Replace("%25", "%");
                strQueryString = strQueryString.Replace("%26", "&");
                strQueryString = strQueryString.Replace("%27", "''");
                strQueryString = strQueryString.Replace("%28", "(");
                strQueryString = strQueryString.Replace("%29", ")");
                strQueryString = strQueryString.Replace("%2A", "*");
                strQueryString = strQueryString.Replace("%2B", "+");
                strQueryString = strQueryString.Replace("%2C", ",");
                strQueryString = strQueryString.Replace("%2D", "-");
                strQueryString = strQueryString.Replace("%2E", ".");
                strQueryString = strQueryString.Replace("%2F", "/");
                strQueryString = strQueryString.Replace("%30", "0");
                strQueryString = strQueryString.Replace("%31", "1");
                strQueryString = strQueryString.Replace("%32", "2");
                strQueryString = strQueryString.Replace("%33", "3");
                strQueryString = strQueryString.Replace("%34", "4");
                strQueryString = strQueryString.Replace("%35", "5");
                strQueryString = strQueryString.Replace("%36", "6");
                strQueryString = strQueryString.Replace("%37", "7");
                strQueryString = strQueryString.Replace("%38", "8");
                strQueryString = strQueryString.Replace("%39", "9");
                strQueryString = strQueryString.Replace("%3A", ":");
                strQueryString = strQueryString.Replace("%3B", ";");
                strQueryString = strQueryString.Replace("%3C", "<");
                strQueryString = strQueryString.Replace("%3D", "=");
                strQueryString = strQueryString.Replace("%3E", ">");
                strQueryString = strQueryString.Replace("%3F", "?");
                strQueryString = strQueryString.Replace("%40", "@");

                strQueryString = strQueryString.Replace("%5B", "[");
                strQueryString = strQueryString.Replace("%5C", "\\");
                strQueryString = strQueryString.Replace("%5D", "]");
                strQueryString = strQueryString.Replace("%5E", "^");
                strQueryString = strQueryString.Replace("%5F", "_");
                strQueryString = strQueryString.Replace("%60", "`");
                
                strQueryString = strQueryString.Replace("%7B", "{");
                strQueryString = strQueryString.Replace("%7C", "|");
                strQueryString = strQueryString.Replace("%7D", "}");
                strQueryString = strQueryString.Replace("%7E", "~");
                strQueryString = strQueryString.Replace("%IO", "H");
                strQueryString = strQueryString.Replace("%A0", " ");
                strQueryString = strQueryString.Replace("%A1", "¡");
                strQueryString = strQueryString.Replace("%A2", "¢");
                strQueryString = strQueryString.Replace("%A3", "£");
                strQueryString = strQueryString.Replace("%A4", "¤");
                strQueryString = strQueryString.Replace("%A5", "¥");
                strQueryString = strQueryString.Replace("%A6", "¦");
                strQueryString = strQueryString.Replace("%A7", "§");
                strQueryString = strQueryString.Replace("%A8", "¨");
                strQueryString = strQueryString.Replace("%A9", "©");
                strQueryString = strQueryString.Replace("%AA", "ª");
                strQueryString = strQueryString.Replace("%AB", "«");
                strQueryString = strQueryString.Replace("%AC", "");
                strQueryString = strQueryString.Replace("%AD", "­");
                strQueryString = strQueryString.Replace("%AE", "®");
                strQueryString = strQueryString.Replace("%AF", "¯");
                strQueryString = strQueryString.Replace("%B0", "°");
                strQueryString = strQueryString.Replace("%B1", "±");
                strQueryString = strQueryString.Replace("%B2", "²");
                strQueryString = strQueryString.Replace("%B3", "³");
                strQueryString = strQueryString.Replace("%B4", "´");
                strQueryString = strQueryString.Replace("%B5", "µ");
                strQueryString = strQueryString.Replace("%B6", "");
                strQueryString = strQueryString.Replace("%B7", "·");
                strQueryString = strQueryString.Replace("%B8", "¸");
                strQueryString = strQueryString.Replace("%B9", "¹");
                strQueryString = strQueryString.Replace("%BA", "º");
                strQueryString = strQueryString.Replace("%BB", "»");
                strQueryString = strQueryString.Replace("%BC", "¼");
                strQueryString = strQueryString.Replace("%BD", "½");
                strQueryString = strQueryString.Replace("%BE", "¾");
                strQueryString = strQueryString.Replace("%BF", "¿");
                strQueryString = strQueryString.Replace("%C0", "À");
                strQueryString = strQueryString.Replace("%C1", "Á");
                strQueryString = strQueryString.Replace("%C2", "Â");
                strQueryString = strQueryString.Replace("%C3", "Ã");
                strQueryString = strQueryString.Replace("%C4", "Ä");
                strQueryString = strQueryString.Replace("%C5", "Å");
                strQueryString = strQueryString.Replace("%C6", "Æ");
                strQueryString = strQueryString.Replace("%C7", "Ç");
                strQueryString = strQueryString.Replace("%C8", "È");
                strQueryString = strQueryString.Replace("%C9", "É");
                strQueryString = strQueryString.Replace("%CA", "Ê");
                strQueryString = strQueryString.Replace("%CB", "Ë");
                strQueryString = strQueryString.Replace("%CC", "Ì");
                strQueryString = strQueryString.Replace("%CD", "Í");
                strQueryString = strQueryString.Replace("%CE", "Î");
                strQueryString = strQueryString.Replace("%CF", "Ï");
                strQueryString = strQueryString.Replace("%D0", "Ð");
                strQueryString = strQueryString.Replace("%D1", "Ñ");
                strQueryString = strQueryString.Replace("%D2", "Ò");
                strQueryString = strQueryString.Replace("%D3", "Ó");
                strQueryString = strQueryString.Replace("%D4", "Ô");
                strQueryString = strQueryString.Replace("%D5", "Õ");
                strQueryString = strQueryString.Replace("%D6", "Ö");
                strQueryString = strQueryString.Replace("%D7", "×");
                strQueryString = strQueryString.Replace("%D8", "Ø");
                strQueryString = strQueryString.Replace("%D9", "Ù");
                strQueryString = strQueryString.Replace("%DA", "Ú");
                strQueryString = strQueryString.Replace("%DB", "Û");
                strQueryString = strQueryString.Replace("%DC", "Ü");
                strQueryString = strQueryString.Replace("%DD", "Ý");
                strQueryString = strQueryString.Replace("%DE", "Þ");
                strQueryString = strQueryString.Replace("%DF", "ß");
                strQueryString = strQueryString.Replace("%E0", "À");
                strQueryString = strQueryString.Replace("%E1", "Á");
                strQueryString = strQueryString.Replace("%E2", "Â");
                strQueryString = strQueryString.Replace("%E3", "Ã");
                strQueryString = strQueryString.Replace("%E4", "Ä");
                strQueryString = strQueryString.Replace("%E5", "Å");
                strQueryString = strQueryString.Replace("%E6", "Æ");
                strQueryString = strQueryString.Replace("%E7", "Ç");
                strQueryString = strQueryString.Replace("%E8", "È");
                strQueryString = strQueryString.Replace("%E9", "É");
                strQueryString = strQueryString.Replace("%EA", "Ê");
                strQueryString = strQueryString.Replace("%EB", "Ë");
                strQueryString = strQueryString.Replace("%EC", "Ì");
                strQueryString = strQueryString.Replace("%ED", "Í");
                strQueryString = strQueryString.Replace("%EE", "Î");
                strQueryString = strQueryString.Replace("%EF", "Ï");
                strQueryString = strQueryString.Replace("%F0", "Ð");
                strQueryString = strQueryString.Replace("%F1", "Ñ");
                strQueryString = strQueryString.Replace("%F2", "Ò");
                strQueryString = strQueryString.Replace("%F3", "Ó");
                strQueryString = strQueryString.Replace("%F4", "Ô");
                strQueryString = strQueryString.Replace("%F5", "Õ");
                strQueryString = strQueryString.Replace("%F6", "Ö");
                strQueryString = strQueryString.Replace("%F7", "÷");
                strQueryString = strQueryString.Replace("%F8", "Ø");
                strQueryString = strQueryString.Replace("%F9", "Ù");
                strQueryString = strQueryString.Replace("%FA", "Ú");
                strQueryString = strQueryString.Replace("%FB", "Û");
                strQueryString = strQueryString.Replace("%FC", "Ü");
                strQueryString = strQueryString.Replace("%FD", "Ý");
                strQueryString = strQueryString.Replace("%FE", "Þ");
                strQueryString = strQueryString.Replace("%FF", "Ÿ");
                strQueryString = strQueryString.Replace("+", " ");
            }
            catch
            {
                strQueryString = strURL;
            }
        }
        else
            strQueryString = strURL;

        return new SqlString(strQueryString);
    }
};