Oracle中如何提取'-'后的数字并对混合字符串按指定规则排序

你原来的SQL仅处理了横杠前的数字部分，未对横杠后的数字做数值排序，同时没有将纯字母类字符串统一后置，因此无法得到预期结果。

调整后的SQL如下（适配Oracle、PostgreSQL等支持正则函数的数据库）：

SELECT column
FROM your_table
ORDER BY
  -- 区分数字格式串和纯字母串，数字类全部前置
  CASE WHEN REGEXP_LIKE(column, '^\d+-\d+$') THEN 0 ELSE 1 END ASC,
  -- 按横杠前的数字升序排序
  TO_NUMBER(REGEXP_SUBSTR(column, '^\d+')) ASC,
  -- 按横杠后的数字升序排序，解决字符串排序中10排在2前的问题
  TO_NUMBER(REGEXP_SUBSTR(column, '\d+$')) ASC,
  -- 纯字母串按字典序自然排序
  column ASC;

如果是MySQL环境，只需把REGEXP_LIKE(column, '^\d+-\d+$')替换为column REGEXP '^[0-9]+-[0-9]+$'即可，其余排序逻辑完全一致。

排序逻辑说明 #

• 第一层排序：用正则判断字符串是否为数字-数字格式，符合的返回0排在前面，纯字母的返回1排在后面
• 第二层排序：提取横杠前的所有数字转为数值排序，保证前缀数字小的排在前面，比如1101开头的所有串排在1104开头的串前
• 第三层排序：提取横杠后的所有数字转为数值排序，避免字符串排序时10出现在2前面的问题
• 第四层排序：纯字母的字符串直接按原字段的字典序排序，刚好符合abc → pulse → shock的预期顺序

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