SQL search with logical operators

Question

I have a asp.net web application that needs a search page that searches for data in a specific table (SQL). Today it only has a simple LIKE query, useing parametized sql:

SqlParameter[] param = new SqlParameter[1];

param[0] = new SqlParameter("@searchText", SqlDbType.VarChar);
param[0].Value = "%" + text + "%";

using (SqlDataReader dr = SqlHelper.ExecuteReader(this.ConnectionString, CommandType.StoredProcedure, "isp_Search", param))
{
       //Do something
}

Now I need to add the option to use logical operators to the search.

So in the textbox a user might search for things like

Adam OR Adams
James AND NOT Jame
Douglas AND (Adam OR Adams)

Adding full-text indexing to the table is not a preferred option since I do not control the data model.

I'm looking for a method of interpreting the text queries and convert into a SQL statement with the appropriate number of SqlParams.

searchexpression = "Douglas AND (Adam OR Adams)"
MakeSearchQuery(searchexpression, out sqlquery, out SqlParam[] params)

Would return somelike like

sqlquery = "SELECT someFields FROM table WHERE someField=@Param1 AND (someField=@Param2 OR someField=@Param3)"

And a sqlParams like

sqlParam[0] = 'Douglas'
sqlParam[1] = 'Adam'
sqlParam[2] = 'Adams'

Now there must be someone that have made something like this before? I've search both SO and Google without any real success. And pointers to either open source/free code or a good idea on how to convert the search expression into SQL is welcome.

Answer 1

The parsing of the query via and/or/() etc needs to be done via some kind of parser. For something this simple, a shunting-yard algorithm should do nicely (and is how we handle the and/or/not etc when filtering SE on tags, although the full support for bracketed sub-expressions is only available internally). This will generate a tree of the operations, i.e. (here using prefix representation, but it is more typically used to produce an AST)

and("Douglas", or("adam", "adams"))

which you then need to use to generate TSQL. Not trivial, but not rocket-science either.