如何在SQL中筛选HPA11至HPA3237间的有效字母数字值

现有临时表数据：

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

需要筛选出仅介于HPA11和HPA3237之间的有效值：要求值以HPA开头，后面紧跟纯数字，且数字范围在11到3237之间，需排除HPA19GA这类带非数字后缀的无效值。

原查询where sortvalue between 'hpa11' and 'hpa3237'会返回HPA19和HPA19GA，无法满足需求，以下是可行的解决方案：

方案一：格式校验+数字范围判断（通用写法） #

先验证值的格式合法性，再提取数字部分做数值范围判断：

select *
from #temp
where 
    -- 确保以HPA开头且紧跟数字
    SortValue like 'HPA[0-9]%'
    -- 确保后面无任何非数字字符
    and SortValue not like 'HPA%[^0-9]%'
    -- 去掉HPA前缀，转成整数后判断范围
    and cast(stuff(SortValue, 1, 3, '') as int) between 11 and 3237

方案二：正则表达式简化判断（适用于SQL Server 2017+） #

利用正则直接校验格式，再提取数字做范围判断：

select *
from #temp
where 
    -- 正则匹配：HPA开头+纯数字结尾
    REGEXP_LIKE(SortValue, '^HPA[0-9]+$')
    -- 去掉HPA前缀转整数，判断范围
    and cast(REGEXP_REPLACE(SortValue, '^HPA', '') as int) between 11 and 3237

方案三：低版本SQL兼容写法 #

如果使用不支持正则的低版本SQL，可通过PATINDEX校验格式：

select *
from #temp
where 
    left(SortValue, 3) = 'HPA'
    -- 校验去掉前缀后的字符串是否全为数字
    and PATINDEX('%[^0-9]%', stuff(SortValue, 1, 3, '')) = 0
    -- 数字范围判断
    and cast(stuff(SortValue, 1, 3, '') as int) between 11 and 3237

以上三种方案均会仅返回HPA19，符合需求。

内容的提问来源于stack exchange，提问作者user1083828