Here's your string example with the proper justification for jusing BETWEEN for such things. It returns the correct value, as expected.

declare @t table(Start varchar(10)

,[End] varchar(10))

insert @t

values('1500','1599')

,('2400','2799')

,(' 1',' 599')

,('2800','2999')

,(' 700',' 799')

,(' A1',' A3')

,(' B4',' B9')

declare @val varchar(10)

set @val = '2609'

select *

from @t

where @val between Start and [End]

Think of a dictionary because that's how strings work for this. If the "1" where an "A" and the "599" were "Egg", where would you the word "Bang" (your 2609) to appear even though it has more letters than either of the other 2? The same holds true for strings that look like numbers.