Issues with items not being found on search
===

I am currently having an issue with my searching, as far as I can tell it is how the string is actually being built but I can't seem to pinpoint the place that the issue is occurring but I have a vague idea of what the issue may be.

Basically the issue is that when you are searching for certain items for example in this case:

CD's

The search will only return the item if the search is input as CDs (ignore case) however no result what so ever is returned if you search for CD's.

I have a feeling that this me down to the query as I am actually making it place the '' twice into the query to escape the ' or else server errors are thrown. The SQL query that is being executed is as follows:

	{SELECT DISTINCT {prod.*}
	FROM tbl_ecom_cat_product {prod}
	   INNER JOIN tbl_ecom_cat_node prod_1_ ON prod_1_.node_id = prod.prod_id
	   LEFT OUTER JOIN tbl_ecom_cat_node_text nt ON prod.prod_id = nt.node_id
	   LEFT OUTER JOIN tbl_ecom_shipping_service AS s ON prod.role = s.shs_id
	WHERE (
		s.shs_id IS NULL
	)
	AND ( prod_1_.visible_on_website = 1 )

	AND ( prod_1_.name LIKE :wildcard0
		OR prod.stock_code LIKE :wildcard0
		OR prod.supplier_part_number LIKE :wildcard0
	)
	UNION
	SELECT DISTINCT {prod.*}
	FROM tbl_ecom_cat_product {prod}
	   INNER JOIN tbl_ecom_cat_node prod_1_ ON prod_1_.node_id = prod.prod_id
	   LEFT OUTER JOIN tbl_ecom_cat_node_text nt ON prod.prod_id = nt.node_id
	   LEFT OUTER JOIN tbl_ecom_shipping_service AS s ON prod.role = s.shs_id
	WHERE (
		s.shs_id IS NULL
	)
	AND ( prod_1_.visible_on_website = 1 )

	AND CONTAINS(nt.text, '" FORMSOF INFLECTIONAL, cd''s """', LANGUAGE 'English')
	}

I have also noticed that within this code there is a random " being inserted for some reason, I am not quite sure where this comes from, below is the code where the SQL string is built and all the error characters are removed.

        private string[] syntaxErrorChars = { "{", "}", "[", "]", "(", ")" };
        private string[] errorChars = { "'", "&", "/", "\\", "~", "@" };
		
		  //Escape search string
            StringBuilder sb = new StringBuilder();

            string[] splitString = fTextSearch.Split(errorChars, StringSplitOptions.None);

            int numNewCharactersAdded = 0;
            foreach (string itm in splitString)
            {
                sb.Append(itm); //append string
                if (fTextSearch.Length > (sb.Length - numNewCharactersAdded))
                {
                    sb.Append(fTextSearch[sb.Length - numNewCharactersAdded]); //append splitting character
                    sb.Append(fTextSearch[sb.Length - numNewCharactersAdded - 1]); //append it again
                    numNewCharactersAdded++;
                }
            }

            if (!string.IsNullOrEmpty(sb.ToString()))
                fTextSearch = sb.ToString();
            //Escaping Finished

            //Remove Syntax breaking chars
            for (int i = 0; i < syntaxErrorChars.Count(); i++)
            {
                fTextSearch = fTextSearch.Replace(syntaxErrorChars[i], string.Empty);
            }
            //Finished Syntax breaking chars


            // Wraps quotes around the search string incase of dangerous keywords
            fTextSearch = Regex.Replace(fTextSearch, ".*(or|and|!|).*", "\"$&\"");

            //Build the common clauses for each half of the union query
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT DISTINCT");
            sql.AppendLine(" {prod.*}");
            sql.AppendLine("FROM tbl_ecom_cat_product {prod}");
            sql.AppendLine("   INNER JOIN tbl_ecom_cat_node prod_1_ ON prod_1_.node_id = prod.prod_id");
            sql.AppendLine("   LEFT OUTER JOIN tbl_ecom_cat_node_text nt ON prod.prod_id = nt.node_id");

            if (!includeSkus)
                sql.AppendLine("   LEFT OUTER JOIN tbl_ecom_cat_sku_product_role AS r ON prod.role = r.prod_id");

            sql.AppendLine("   LEFT OUTER JOIN tbl_ecom_shipping_service AS s ON prod.role = s.shs_id");
            sql.AppendLine("WHERE (");
            sql.AppendLine("    s.shs_id IS NULL");
            sql.AppendLine(")");

            if (!includeSkus)
            {
                sql.AppendLine("AND (");
                sql.AppendLine("    r.prod_id IS NULL");
                sql.AppendLine(")");
            }
            if (isPublicFacing)
                sql.AppendLine("AND ( prod_1_.visible_on_website = 1 )");
            
            string commonClause = sql.ToString();

            //Build the nasty query
            sql = new StringBuilder();
            sql.AppendLine(commonClause);

			
            //Union with the full text search
            if (!string.IsNullOrEmpty(fTextSearch))
                {
                    sql.AppendLine("UNION");
                    sql.AppendLine(commonClause);
                    sql.AppendLine(string.Format("AND CONTAINS(nt.text, '{0}', LANGUAGE 'English')", fTextSearch));
                }
           
            IQuery query = session.CreateSQLQuery(sql.ToString()).AddEntity("prod", typeof(Product));

            for (int i = 0; i < searchWords.Length; i++)
            {
                query = query.SetString(string.Format("wildcard{0}", i.ToString()), AddWildcards(searchWords[i]));
            }

            IList<Product> results = query.List<Product>();

