PostgreSQL中如何对含数字的字符串列进行自然排序？

针对你的Tasks表中code字段的排序需求，核心思路是将字符串拆分为非数字前缀和数字部分，先按前缀排序，再把数字部分转换为数值类型排序（数值排序会遵循自然顺序，而非字典序）。以下是不同主流数据库的具体实现方案：

PostgreSQL #

方案1：通用正则拆分前缀与数字 #

select "code" from "Tasks"
order by 
  -- 提取所有非数字前缀
  regexp_replace("code", '\d+$', '', 'g'),
  -- 提取末尾数字并转为整数排序
  cast(regexp_replace("code", '^\D+', '', 'g') as integer) asc;

方案2：精准匹配固定格式（如TASK-X） #

如果code格式固定为前缀-数字，可以用更精准的正则分组：

select "code" from "Tasks"
order by 
  (regexp_match("code", '^(\D+)-(\d+)$'))[1],
  cast((regexp_match("code", '^(\D+)-(\d+)$'))[2] as integer) asc;

MySQL/MariaDB #

方案1：针对固定分隔符（如TASK-X） #

SELECT `code` FROM `Tasks`
ORDER BY
  -- 提取分隔符'-'前的前缀
  SUBSTRING_INDEX(`code`, '-', 1),
  -- 提取分隔符'-'后的数字并转为无符号整数排序
  CAST(SUBSTRING_INDEX(`code`, '-', -1) AS UNSIGNED) ASC;

方案2：通用正则提取（前缀不固定时） #

SELECT `code` FROM `Tasks`
ORDER BY
  -- 移除所有数字，保留前缀
  REGEXP_REPLACE(`code`, '[0-9]+', ''),
  -- 提取所有数字并转为整数排序
  CAST(REGEXP_SUBSTR(`code`, '[0-9]+') AS UNSIGNED) ASC;

SQL Server #

SELECT "code" FROM "Tasks"
ORDER BY
  -- 提取第一个数字前的前缀
  SUBSTRING("code", 1, PATINDEX('%[0-9]%', "code") - 1),
  -- 提取从第一个数字开始的部分并转为整数排序
  CAST(SUBSTRING("code", PATINDEX('%[0-9]%', "code"), LEN("code")) AS INT) ASC;

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