Searching lists, ranges

Question

I have an ASP.NET application which needs to search a VARCHAR(20) column using lists and ranges. In other words, the input could be something like:

ABC,444,CD-EF,90-BA,HIJ

and the result needs to be the equivalent of:

SELECT * FROM table
WHERE
  Col1 = 'ABC' OR
  Col1 = '444' OR
  Col1 BETWEEN 'CD' AND 'EF' OR
  Col1 BETWEEN '90' AND 'BA' OR
  Col1 = 'HIJ'

The regular SQL column sorting order is acceptable for the ranges. There are two main pieces involved here:

1. Sending the parameters from .NET to a stored procedure.
2. Using the parameters in the SP to do a search.

Some options I have considered, which are not mutually exclusive:

1. I could send the string as is. There is no array[] = SPLIT(',', @query) or similar, so parsing would be low level. I would rather do parsing on the .NET side.
2. On .NET side, convert to XML, in SP convert to table.
3. Use a cursor to go through the parameters that are already in table, do separate queries, and merge the results.
4. Create a dynamic where clause (on SQL side with #1, or on .NET side)

Dynamic SQL on the .NET side seems the "easy" way out, but I'm not convinced it's the best. Any thoughts?

Answer 1

I think creating the SQL statement on the .NET side is more than the easy way out. SQL performs best when doing set-base operations (e.g., running a SELECT statement). SQL is not the best choice when doing procedural coding.

When addressing each of your options other than creating dynamic SQL on .NET side, you are asking SQL to do procedural stuff.

Since you can have .NET do the procedural stuff and SQL Server do the set-based stuff, that's the way to go.

Answer 2

To accomplish this in SQL Server, you'll need means of splitting a string/VARCHAR into a temporary table which can then use for JOINs to filter the target table.

See this excellent MSDN article on constructing queries using encoded input. Includes an SQL function for splitting character-delimited VARCHARs:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringArrayInput&referringTitle=Home

This will work very well for equality comparisons - the BETWEEN operations are going to be trickier to implement. Given the limitations to SQL string manipulation compared to C#, I'd end up implementing this in C#.

Answer 3

How about just creating the SQL statement in C#. It implies you have control over your input string so that you're not prone to SQL injection attacks.

string para = "ABC,444,CD-EF,90-BA,HIJ";
StringBuilder sb = new StringBuilder("SELECT * FROM table WHERE ");
List<string> queries = new List<string>();
foreach (var part in para.Split(','))
{
    if (part.Contains("-"))
    {
        var between = part.Split('-');
        queries.Add(string.Format("Col1 BETWEEN '{0}' AND '{1}'", between[0], between[1]));
    }
    else
    {
        queries.Add(string.Format("Col1 = '{0}'", part));
    }
}
sb.Append(string.Join(" OR ", queries.ToArray()));
string sql = sb.ToString();