SQL中含数字的字符串按数值排序的问题求助

你之前用REPLACE(code,' ','')排序无效，是因为即使去掉空格，数据库还是按字符串字典序排序（比如"10"会排在"2"前面，因为字符'1'比'2'小）。要实现按数字数值排序，核心是提取字符串中的数字部分并转换为数值类型，再以此排序。以下是主流数据库的实现方法：

一、字符串仅包含数字和空格 #

如果字段内容只有数字和空格（比如" 123 "、"45 67"），只需去掉空格后转换为数值类型：

MySQL/MariaDB #

SELECT * FROM testTbl ORDER BY CAST(REPLACE(code, ' ', '') AS UNSIGNED);

SQL Server #

SELECT * FROM testTbl ORDER BY CAST(REPLACE(code, ' ', '') AS INT);

PostgreSQL #

SELECT * FROM testTbl ORDER BY CAST(REPLACE(code, ' ', '') AS INTEGER);

二、字符串包含非数字字符（如前缀/后缀文本） #

如果字段里混有非数字内容（比如"ITEM 10"、"ABC-2"），需要先提取连续的数字部分，再转换为数值：

MySQL/MariaDB #

用REGEXP_SUBSTR提取第一个连续数字段：

SELECT * FROM testTbl ORDER BY CAST(REGEXP_SUBSTR(code, '[0-9]+') AS UNSIGNED);

SQL Server #

用PATINDEX定位数字起始位置，再截取数字部分：

SELECT * FROM testTbl
ORDER BY CAST(SUBSTRING(code, PATINDEX('%[0-9]%', code), LEN(code)) AS INT);

PostgreSQL #

用正则匹配提取数字：

SELECT * FROM testTbl ORDER BY CAST(SUBSTRING(code FROM '\d+') AS INTEGER);

三、处理无数字的异常情况 #

如果部分字段没有数字，直接转换会报错，可以用TRY_CAST（支持的数据库）处理，转换失败时返回NULL（排序时NULL默认排在最前或最后，可根据需求调整）：

MySQL/MariaDB #

SELECT * FROM testTbl ORDER BY TRY_CAST(REGEXP_SUBSTR(code, '[0-9]+') AS UNSIGNED);

SQL Server #

SELECT * FROM testTbl
ORDER BY TRY_CAST(SUBSTRING(code, PATINDEX('%[0-9]%', code), LEN(code)) AS INT);

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