Get max integer value from varchar field using Between claus

Question

HI, I have a table where the varchar field is used to store a alphanumeric values. I want to get the MAX value from the field without considering the alpha numeric values.

 

E.g

create table #Temp

(

id int,

number Varchar(50),

)

truncate table #Temp

insert into #Temp values(1,'100001')

insert into #Temp values(1,'100000')

insert into #Temp values(1,'12')

insert into #Temp values(1,'8')

insert into #Temp values(1,'5')

insert into #Temp values(1,'1d8')

insert into #Temp values(1,'20')

insert into #Temp values(1,'100000-01425')

 

 select * from #Temp

 

When I select the row i will get all row, now I want MAX value of the records having only numeric values which should be in selected range.

I have tried following query but didn't worked as expected.

 It returns 100001 as output.

 

SELECT MAX(CAST(c.number as int)) [CurrentSourceKey]

FROM #Temp c

WHERE c.number not like '%[a-z]%' AND

ISNUMERIC(c.number) = 1 AND

c.number BETWEEN '1' AND '20'

 

Output should be

 21

 

please help.

Thanks