T-Sql Like Dilemma - selecting rows w.r.t. keyword

_{This is a sequel to my (other) dumb question that I have asked today}

I have a stored proc in SQL Server which needs to select certain rows based on a keyword, like:

@Key varchar(5) = 'a'

select * From Products where ProductName like 'a%'

now, the @Key can contain values like 0-9 and XYZ apart from all the other normal characters from A, B to W

so when @Key is XYZ it needs to select all products which starts from letters X, Y, OR Z and when its 0-9 it should select any products which starts from numbers.

so what I have is this now:

WHERE
(@Key='xyz' and (name like 'x%' or name like 'y%' or name like 'z%'))
or name LIKE @Keyword +'%'

and now, to get the proc to pickup numbers, I should do something similar to the XYZ but includes name like '0%' o开发者_如何学编程r name like '1%' ten times.

Surely, there should be a better way to ask SQL Server to select with names starting from decimal?

is this the only way to do it?

thanks.

_{sorry if I am asking too many questions!}

The LIKE statement also allows for wild cards in a single character, eg

SELECT...
WHERE foo LIKE '[0-9]%'

will return values where foo begins with a digit.

Similarly,

SELECT...
WHERE foo LIKE '[aeiou]%'

will return values where foo begins with a vowel.

Check the documentation for LIKE - there are a few more tricks...

You can use

name like '[0-9]%'

For your other scenario you can use

name like '[x-z]%'

or

name like '[xyz]%'

In a case insensitive collation these last 2 are equivalent. However they are not the same in a case sensitive collation ([x-z]% would match "Yoghurt" whereas [xyz]% wouldn't)