MySQL如何将varchar类型带前缀的金额字符串按数值排序

核心逻辑为先定位字符串中$符号的位置，截取$后的内容转为数值后排序，可同时兼容A$前缀和$前缀的两种数据格式，无需修改原有表结构。

各数据库实现写法 #

• MySQL 写法：

SELECT text FROM A 
ORDER BY CAST(SUBSTRING(text, LOCATE('$', text) + 1) AS DECIMAL(10,2)) ASC;

• PostgreSQL 写法：

SELECT text FROM A 
ORDER BY SUBSTRING(text, strpos(text, '$') + 1)::numeric ASC;

• SQL Server 写法：

SELECT text FROM A 
ORDER BY CAST(SUBSTRING(text, CHARINDEX('$', text) + 1, LEN(text)) AS DECIMAL(10,2)) ASC;

• Oracle 写法：

SELECT text FROM A 
ORDER BY TO_NUMBER(SUBSTR(text, INSTR(text, '$') + 1)) ASC;

原理解释 #

1. 首先通过数据库自带的字符串查找函数定位$的下标，不受前缀是否带A的影响
2. 从$的下一位开始截取，得到纯数字字符串
3. 将纯数字字符串转为数值类型后排序，即可得到预期的数值大小排序结果

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