I have the following data:

create table #temp (SortValue varchar(100))
insert into #temp
values
('511111'),
('211'),
('HPA19'),
('IPA-255'),
('IPA-223'),
('HPA19GA')

I'd like to find only the values between HPA11 and HPA3237.

If I try:

select *
from #temp
where sortvalue between 'hpa11' and 'hpa3237'

I receive results:

HPA19,
HPA19GA

However, I'm trying to find a way for HPA19GA to NOT show up in my results. I (think) I understand why SQL is returning this as a result between HPA11 and HPA3237. However, in my data set I know that only HPA11 through HPA3237 and I need to know that HPA19GA is invalid and does not fall between my min/max values.

I have tried the following:

select *
from #temp
where right('0000000000' + sortvalue, 10)
between '00000hpa11' and '0000hpa237'

This returns 511111, HPA19

I also tried:
select *
from #temp
where  SUBSTRING (sortvalue, PATINDEX ('% [0-9]%',sortvalue), LEN (sortvalue))
between 'HPA11' and 'HPA3237'

It returns:

HPA19GA

I also tried:

select *
from #temp
where left(concat(sortvalue, '0000000000'), 10)
between 'HPA1100000' and 'HPA3237000'

It Returns:

HPA19,
HPA19GA

If anyone has a brilliant idea I would love to see what someone could come up with!