title: T-SQL Case statement utilizing substring and isnumeric
body: <p>I have a T-SQL stored proc that supplies a good amount of data to a grid on a .NET page....so much so that I put choices at the top of the page for "0-9" and each letter in the alphabet so that when the user clicks the letter I want to filter my results based on results that begin with that first letter.  Let's say we're using product names.  So if the user clicks on "A" I only want my stored proc to return results where SUBSTRING(ProductName, 1, 1) = "A".</p>
  
  <p>Where I'm getting hung up is on product names that begin with a number.  In that case I want to fetch all ProductName values where ISNUMERIC(SUBSTRING(ProductName, 1, 1)) = 1.  I'm using an input parameter called @FL.  @FL will either be a zero (we have few products that begin with numerics, so I lump them all together this way).</p>
  
  <p>Of course there's also the alternative of WHERE SUBSTRING(ProductName, 1, 1) IN ('0', '1', '2'.....) but even then, I've never been able to devise a CASE statement that will do an = on one evaluation and an IN statement for the other.</p>
  
  <p>Here's what I have in my proc for the CASE part of my WHERE clause.  It doesn't work, but it may be valuable if only from a pseudocode standpoint.</p>
  
  <p>Thanks in advance for any ideas you may have.</p>
  
  <pre><code>AND CASE @FL
      WHEN "0" THEN
      CASE WHEN @FL = "0" THEN
          isnumeric(substring(dbo.AssnCtrl.Name, 1, 1)) = 1
      ELSE
          SUBSTRING(dbo.AssnCtrl.Name, 1, 1) = @FL            
      END
  END
  </code></pre>
  
  <p>*** I know that this use of the CASE statement is "non-standard", but I found it online and thought it had some promise.  But attempts to use a single CASE statement yielded the same result (an error near '=').</p>