T-SQL Case statement utilizing substring and isnumeric

Question

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".

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).

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.

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.

Thanks in advance for any ideas you may have.

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

* 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 '=').

Answer 1

Why not just use Like operator ?

   Where dbo.AssnCtrl.Name Like @FL + '%' 

When they select the Any Number option, pass in @FL as '[0-9]'

(I assume you have an index on this name column ?)

Answer 2

To steal a bit from Charles:

AND Substring(dbo.AssnCtrl.Name, 1, 1) Like
  CASE WHEN @FL = '0' THEN '[0-9]'
    ELSE @FL            
  END

Answer 3

Have you tried

AND (
        isnumeric(substring(dbo.AssnCtrl.Name, 1, 1)) = 1
    or
        SUBSTRING(dbo.AssnCtrl.Name, 1, 1) = @FL    )

this works for me:

select * from casefile where
isnumeric(substring(pmin,1,1)) = 1
or 
substring(pmin,1,1) = 'a'

Answer 4

Thanks for the great suggestions everyone! Cheers.