This solution has been marked down, not sure why this would be be case.
Maybe it is due to the solution using a pattern matching technique to find your desired result.
In any case this will get you your desired result using pattern matching technique and not a range condition.
-----
In your question you have stated you want to find out if two character are between two ranges.
But you have stated 3 different ranges:
aa to a9
AA to Ä9
AA to A9
You are mixing your case, accent and sensitivity.
Collation and Unicode Support[
^]
To answer your question lets consider AA to A9.
You can use PATINDEX with collation Latin1_General_CS_AS to get you desired result.
Here is a bit of sql to demonstrate (also includes results for some other collation):
with ATab as (
select 'A1' input
union all select 'a1'
union all select 'Ä1'
union all select 'b1'
union all select 'ba'
union all select 'AZ'
union all select 'A9'
union all select 'A9A'
union all select 'BA1'
)
select
Input,
patindex('A[A-Z,0-9]', Input COLLATE Latin1_General_CS_AS) PatternPosition,
'Latin1_General_CS_AS' CollationUsed
from ATab
union all
select
Input,
patindex('A[A-Z,0-9]', Input COLLATE Latin1_General_CS_AI) PatternPosition,
'Latin1_General_CS_AI' CollationUsed
from ATab
union all
select
Input,
patindex('A[A-Z,0-9]', Input COLLATE Latin1_General_CI_AS) PatternPosition,
'Latin1_General_CI_AS' CollationUsed
from ATab
union all
select
Input,
patindex('A[A-Z,0-9]', Input COLLATE Latin1_General_CI_AI) PatternPosition,
'Latin1_General_CI_AI' CollationUsed
from ATab
;
Hope that helps you out.
Also have a read of these for PATINDEX:
PATINDEX (Transact-SQL)[
^]
Using SQL Server's CHARINDEX and PATINDEX[
^]