SQL Select Like Keywords in Any Order

I am building a Search function for a shopping cart site, which queries a SQL Server database. When the user enters "Hula Hoops" in the search box, I want results for all records containing both "Hula" and "Hoop", in any order. Furthermore, I need to search multiple columns (i.e. ProductName, Description, ShortName, MaufacturerName, etc.)

All of these product names should be returned, when searching for "H开发者_如何学Cula hoop":

• Hula hoop
• Hoop Hula
• The Hoopity of xxhula sticks

(Bonus points if these can be ordered by relevance!)

It sounds like you're really looking for full-text search, especially since you want to weight the words.

In order to use LIKE, you'll have to use multiple expressions (one per word, per column), which means dynamic SQL. I don't know which language you're using, so I can't provide an example, but you'll have to produce a statement that's like this:

For "Hula Hoops":

where (ProductName like '%hula%' or ProductName like '%hoops%')
  and (Description like '%hula%' or Description like '%hoops%')
  and (ShortName like '%hula%' or ShortName like '%hoops%')

etc.

Unfortunately, that's really the only way to do it. Using Full Text Search would allow you to reduce your criteria to one per column, but you'll still have to specify the columns explicitly.

Since you're using SQL Server, I'm going to hazard a guess that this is a C# question. You'd have to do something like this (assuming you're constructing the SqlCommand or DbCommand object yourself; if you're using an ORM, all bets are off and you probably wouldn't be asking this anyway):

SqlCommand command = new SqlCommand();
int paramCount = 0;

string searchTerms = "Hula Hoops";

string commandPrefix = @"select *

from Products";

StringBuilder whereBuilder = new StringBuilder();

foreach(string term in searchTerms.Split(' '))
{
    if(whereBuilder.Length == 0)
    {
        whereBuilder.Append(" where ");
    }
    else
    {
        whereBuilder.Append(" and ");
    }

    paramCount++;

    SqlParameter param = new SqlParameter(string.Format("param{0}",paramCount), "%" + term + "%");

    command.Parameters.Add(param);

    whereBuilder.AppendFormat("(ProductName like @param{0} or Description like @param{0} or ShortName like @param{0})",paramCount);
}

command.CommandText = commandPrefix + whereBuilder.ToString();

SQL Server Full Text Search should help you out. You will basically create indexes on the columns you want to search. in the where clause of your query you will use the CONTAINS operator and pass it your search input.

you can start HERE or HERE to learn more

You might want to check out SOLR too - if you're going to be doing this type of searching. Super cool.

http://lucene.apache.org/solr/