如何修改SQL以兼容新旧Wave名称格式获取最新Wave名称？

问题背景 #

旧Wave名称结构：XXX_XXX_XXXXXXXXXX.XXXX_<递增数字>
示例：XXX_XXX_XXXXXXXXXX.XXXX_1、XXX_XXX_XXXXXXXXXX.XXXX_2

新Wave名称结构：XXXX_XXXXXX_XXX.XXXXXXXXX_XXXX_XXXX_<递增数字>_<年月>
示例：XXXX_XXXXXX_XXX.XXXXXXXXX_XXXX_XXXX_81_042122、XXXX_XXXXXX_XXX.XXXXXXXXX_XXXX_XXXX_80_032122

原有SQL仅能提取旧格式中的递增数字排序，无法适配新格式，需调整逻辑同时兼容两种结构。

解决方案 #

以下SQL通过反向查找下划线位置，自动识别格式并提取对应递增数字，实现兼容：

SELECT TOP 1 wave
FROM Outbound co
WHERE Program = :Program
ORDER BY 
    CAST(
        CASE
            -- 检测最后一段是否为整数（旧格式特征）
            WHEN TRY_CAST(RIGHT(Wave, CHARINDEX('_', REVERSE(Wave)) - 1) AS INT) IS NOT NULL
                THEN RIGHT(Wave, CHARINDEX('_', REVERSE(Wave)) - 1)
            -- 提取倒数第二段的数字（新格式特征）
            ELSE SUBSTRING(
                    Wave,
                    -- 定位倒数第二个下划线的起始位置
                    LEN(Wave) - CHARINDEX('_', REVERSE(Wave), CHARINDEX('_', REVERSE(Wave)) + 1) + 1,
                    -- 计算数字段的长度
                    CHARINDEX('_', REVERSE(Wave), CHARINDEX('_', REVERSE(Wave)) + 1) - CHARINDEX('_', REVERSE(Wave)) - 1
                )
        END AS INT
    ) DESC;

逻辑说明 #

1. 反向查找定位：用REVERSE反转字符串后，从末尾开始找下划线，比正向嵌套查找更简洁可靠。
2. 格式识别：通过TRY_CAST尝试把最后一段转为整数，成功则判定为旧格式，直接取该段作为排序依据；失败则判定为新格式，取倒数第二段的数字。
3. 排序取最新：将提取到的数字转为整数后倒序排序，取第一条即为最新的Wave。

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