如何对varchar类型Cardnumber字段内的数字进行升序排序

核心思路 #

• 把Cardnumber字段的字符串拆分为单个数字字符的独立行
• 对拆分后的字符按升序排序
• 将排序后的字符重新拼接为完整字符串

不同数据库实现代码 #

MySQL 8.0+ #

WITH RECURSIVE split_chars AS (
    SELECT 
        Cardnumber,
        1 AS pos,
        SUBSTRING(Cardnumber, 1, 1) AS char_val
    FROM Employees
    -- 如需处理特定行可加WHERE条件，例：WHERE id = 123
    UNION ALL
    SELECT 
        sc.Cardnumber,
        sc.pos + 1,
        SUBSTRING(sc.Cardnumber, sc.pos + 1, 1)
    FROM split_chars sc
    WHERE sc.pos < LENGTH(sc.Cardnumber)
)
SELECT 
    GROUP_CONCAT(char_val ORDER BY char_val ASC SEPARATOR '') AS sorted_cardnumber
FROM split_chars
GROUP BY Cardnumber;

PostgreSQL #

SELECT 
    string_agg(c, '' ORDER BY c) AS sorted_cardnumber
FROM Employees,
     string_to_table(Cardnumber, NULL) AS c
-- 如需处理特定行可加WHERE条件
GROUP BY Cardnumber;

SQL Server 2017+ #

WITH split_chars AS (
    SELECT 
        Cardnumber,
        1 AS pos,
        SUBSTRING(Cardnumber, 1, 1) AS char_val
    FROM Employees
    UNION ALL
    SELECT 
        sc.Cardnumber,
        sc.pos + 1,
        SUBSTRING(sc.Cardnumber, sc.pos + 1, 1)
    FROM split_chars sc
    WHERE sc.pos < LEN(sc.Cardnumber)
)
SELECT 
    STRING_AGG(char_val, '') WITHIN GROUP (ORDER BY char_val ASC) AS sorted_cardnumber
FROM split_chars
GROUP BY Cardnumber;

Oracle 11gR2+ #

SELECT 
    LISTAGG(SUBSTR(Cardnumber, LEVEL, 1)) WITHIN GROUP (ORDER BY SUBSTR(Cardnumber, LEVEL, 1)) AS sorted_cardnumber
FROM Employees
CONNECT BY LEVEL <= LENGTH(Cardnumber)
AND PRIOR id = id 
AND PRIOR SYS_GUID() IS NOT NULL
-- 如需处理特定行可加额外WHERE条件
GROUP BY Cardnumber;

注意事项 #

• 数字字符的字符排序和数字排序结果一致，无需额外转换为数字类型
• 若Cardnumber存在非数字字符，可先加正则过滤逻辑排除异常数据
• 如需更新原表数据，可将查询结果关联主键执行UPDATE操作

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