You can pad the string values for proper sorting using the REPLICATE operator.

For example:

DECLARE @val VARCHAR(10)

SET @val = '2609'

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

SELECT

Start

,[End]

FROM

@t

WHERE

@val BETWEEN [Start] AND [End]

Your output:

StartEnd

24002799

1599

SELECT

r.[Start]

,r.[End]

FROM

(

SELECT

[Start]+REPLICATE('0',6-LEN([Start])) AS [Start]

,[End]+REPLICATE('0',6-LEN([End])) AS [End]

FROM

@t

) r

WHERE

@val BETWEEN [Start] AND [End]

Output padded right:

StartEnd

240000279900

100000599000

SET @val = '002609'

SELECT

r.[Start]

,r.[End]

FROM

(

SELECT

REPLICATE('0',6-LEN([Start]))+[Start] AS [Start]

,REPLICATE('0',6-LEN([End]))+[End] AS [End]

FROM

@t

) r

WHERE

@val BETWEEN [Start] AND [End]

Output padded left:

StartEnd

002400002799