在Oracle 12中，可以将字符串拆分为前缀和后缀，然后使用MATCH_RECOGNIZE高效地执行逐行模式匹配：

SELECT prefix || first_suffix || '-' || prefix || last_suffix AS range
FROM   (
  SELECT TRANSLATE(ref_code, 'A0123456789', 'A') AS prefix,
         TO_NUMBER(TRANSLATE(ref_code, '0ABCDEFGHIJKLMONPQRSTUVWXYZ', '0')) AS suffix
  FROM   xx_ref_codes
)
MATCH_RECOGNIZE(
  PARTITION BY prefix
  ORDER BY suffix
  MEASURES
    FIRST(suffix) AS first_suffix,
    LAST(suffix) AS last_suffix
  PATTERN (consecutive* final_row)
  DEFINE consecutive AS suffix + 1 = NEXT(suffix)
);

或者，如果要使用分析函数，然后进行聚合，则：

SELECT prefix || MIN(suffix) || '-' || prefix || MAX(suffix) AS range
FROM   (
  SELECT prefix,
         suffix,
         suffix - ROW_NUMBER() OVER (PARTITION BY prefix ORDER BY suffix) AS grp
  FROM   (
    SELECT TRANSLATE(ref_code, 'A0123456789', 'A') AS prefix,
           TO_NUMBER(TRANSLATE(ref_code, '0ABCDEFGHIJKLMONPQRSTUVWXYZ', '0')) AS suffix
    FROM   xx_ref_codes
  )
)
GROUP BY prefix, grp

对于示例数据，这两个函数都输出：
| 范围|
| - ------|
| A1至A5|
| A8至A12|
| B3-B3|
| B5-B5|
| B7-B9|
| 小行星110 - 112|
| 碳1-碳8|
fiddle

也可以使用标准SQL解决：

select header || min(num) || case when min(num) <> max(num) then '-' || header || max(num) end as result 
from (
    select substr(ref_code,1,1) as header, to_number(substr(ref_code,2)) as num,
        to_number(substr(ref_code,2)) - row_number() over(partition by substr(ref_code,1,1) order by to_number(substr(ref_code,2))) as grp, ref_code
    from xx_ref_codes
)
group by header, grp
;

A1-A5
A8-A12
B3
B5
B7-B9
B110-B112
C1-C8