title: Searching lists, ranges
body: <p>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:</p>
  
  <pre><code>ABC,444,CD-EF,90-BA,HIJ
  </code></pre>
  
  <p>and the result needs to be the equivalent of:</p>
  
  <pre><code>SELECT * FROM table
  WHERE
    Col1 = 'ABC' OR
    Col1 = '444' OR
    Col1 BETWEEN 'CD' AND 'EF' OR
    Col1 BETWEEN '90' AND 'BA' OR
    Col1 = 'HIJ'
  </code></pre>
  
  <p>The regular SQL column sorting order is acceptable for the ranges.  There are two main pieces involved here:</p>
  
  <ol>
  <li>Sending the parameters from .NET to a stored procedure.</li>
  <li>Using the parameters in the SP to do a search.</li>
  </ol>
  
  <p>Some options I have considered, which are not mutually exclusive:</p>
  
  <ol>
  <li>I could send the string as is.  There is no <code>array[] = SPLIT(',', @query)</code> or similar, so parsing would be low level.  I would rather do parsing on the .NET side.</li>
  <li>On .NET side, convert to XML, in SP convert to table.</li>
  <li>Use a cursor to go through the parameters that are already in table, do separate queries, and merge the results.</li>
  <li>Create a dynamic where clause (on SQL side with #1, or on .NET side)</li>
  </ol>
  
  <p>Dynamic SQL on the .NET side seems the "easy" way out, but I'm not convinced it's the best.  Any thoughts?</p>