如何将varchar字段提取的子串转换为integer类型实现数据排序？

错误原因 #

你遇到的报错是因为固定长度裁剪的方式兼容性差：一旦存在编号长度和预期不符、或是存在不符合格式的脏数据，substring裁剪结果会出现空字符串，无法直接转换为integer类型。

最优解决方案 #

推荐用正则表达式提取目标数字，不依赖固定长度，适配性更强，以PostgreSQL语法为例（报错格式匹配PostgreSQL特征）：

1. 基础可用写法 #

匹配固定前缀No.SKF.后的第一个连续数字序列，直接转换为整数排序：

SELECT "NO_SURAT" 
FROM account_information 
ORDER BY CAST(substring("NO_SURAT" from 'No\.SKF\.(\d+)') AS INTEGER);

2. 兼容脏数据写法 #

如果使用PostgreSQL 12及以上版本，可以用TRY_CAST避免脏数据导致的SQL报错，转换失败的记录默认排在最后：

SELECT "NO_SURAT" 
FROM account_information 
ORDER BY TRY_CAST(substring("NO_SURAT" from 'No\.SKF\.(\d+)') AS INTEGER) NULLS LAST;

如果你使用的是MySQL等其他数据库，替换为对应数据库的正则提取函数即可，上述正则匹配规则通用。

验证方法 #

你可以先执行以下查询确认提取的数字是否正确，排查不符合格式的脏数据：

SELECT 
  "NO_SURAT",
  substring("NO_SURAT" from 'No\.SKF\.(\d+)') AS extracted_num
FROM account_information;

正则规则说明：No\.SKF\.(\d+)中\.是转义匹配点号，(\d+)是捕获前缀后第一个连续数字序列，完全适配你给出的编号格式。

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