Oracle数据库字母数字混合类型列值正确排序方案求助

原排序SQL不生效的核心原因是CASE表达式要求所有分支返回值的数据类型一致，Oracle中NUMBER类型优先级高于字符串类型，因此原写法会强制将非数字分支的字符串值隐式转换为NUMBER类型，既可能触发类型转换报错，也会导致数字、非数字值的排序优先级混杂，无法实现「纯数字先按数值排序、非数字后按字符串排序」的规则。

将排序逻辑拆分为3个独立排序键，避免同个CASE分支返回不同数据类型：

• 第一排序键先做分组标记：纯数字标记为0、非数字标记为1，保证所有纯数字值整体排在非数字值之前
• 第二排序键仅对纯数字生效，将其转为数值类型按大小排序，非数字值返回NULL不参与该维度排序
• 第三排序键仅对非数字生效，按原字符串字典序排序，纯数字值返回NULL不参与该维度排序

如果保留已定义的is_numeric自定义函数，排序语句写法如下：

ORDER BY
  -- 第一键：区分纯数字/非数字分组，纯数字排前面
  CASE WHEN is_numeric(TRIM(col1)) = 1 THEN 0 ELSE 1 END,
  -- 第二键：纯数字按数值大小排序
  CASE WHEN is_numeric(TRIM(col1)) = 1 THEN to_number(TRIM(col1)) END,
  -- 第三键：非数字按字符串字典序排序
  CASE WHEN is_numeric(TRIM(col1)) != 1 THEN col1 END

如果不想额外维护自定义函数，可以直接用Oracle内置正则函数替换纯数字判断逻辑，无需依赖自定义函数：

ORDER BY
  CASE WHEN REGEXP_LIKE(TRIM(col1), '^[0-9]+$') THEN 0 ELSE 1 END,
  CASE WHEN REGEXP_LIKE(TRIM(col1), '^[0-9]+$') THEN to_number(TRIM(col1)) END,
  CASE WHEN NOT REGEXP_LIKE(TRIM(col1), '^[0-9]+$') THEN col1 END

语句中加TRIM()是为了处理原始数据中部分值带前后空格的情况，避免空格导致纯数字判断失效。

上述写法执行后会完全返回期望的排序结果：

• 纯数字组按数值升序排列：1、2、11、55、60、72、75、77、82、85、90、118
• 非数字组按字符串字典序升序排列：3B1、3B2、PORT/0/635、u1

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