SQL Server中提取varchar列数字部分最大值的方法求助

原SQL返回'S999'而非预期的'S1000'，核心原因是字符串排序为逐字符比较：'S999'的第三个字符是'9'，ASCII值高于'S1000'的第三个字符'1'，因此字符串类型的MAX()会优先返回'S999'。

以下是几种简便的解决方法：

方法1：固定前缀（如统一以'S'开头） #

直接截取数字部分转为数值类型取最大值，再拼接前缀：

SELECT 'S' + CAST(MAX(CAST(SUBSTRING(SerialNo, 2, LEN(SerialNo)-1) AS INT)) AS VARCHAR(10)) AS MaxSerialNo
FROM #TemporaryTable

• SUBSTRING(SerialNo, 2, LEN(SerialNo)-1)：截取从第2位开始的数字部分
• CAST(...) AS INT：将数字部分转为数值类型，按数值逻辑比较大小
• 最后将最大数值转回字符串并拼接前缀'S'

方法2：前缀不固定（支持多种字母前缀） #

如果SerialNo前缀可能变化（如'A123'、'B456'），可以先提取前缀和数字部分，分组取每个前缀下的最大数字：

SELECT 
    LEFT(SerialNo, PATINDEX('%[0-9]%', SerialNo)-1) 
    + CAST(MAX(CAST(SUBSTRING(SerialNo, PATINDEX('%[0-9]%', SerialNo), LEN(SerialNo)) AS INT)) AS VARCHAR(10)) AS MaxSerialNo
FROM #TemporaryTable
GROUP BY LEFT(SerialNo, PATINDEX('%[0-9]%', SerialNo)-1)

• PATINDEX('%[0-9]%', SerialNo)：定位第一个数字的位置
• LEFT(...)：提取数字前的前缀部分，按前缀分组
• 同样将数字部分转为数值取最大后拼接前缀

方法3：兼容格式异常数据 #

如果存在不符合格式的SerialNo（如无数字部分），用TRY_CAST避免转换报错：

SELECT 'S' + CAST(MAX(TRY_CAST(SUBSTRING(SerialNo, 2, LEN(SerialNo)-1) AS INT)) AS VARCHAR(10)) AS MaxSerialNo
FROM #TemporaryTable

• TRY_CAST会将转换失败的结果返回NULL，不影响整体最大值计算

内容的提问来源于stack exchange，提问作者E. A. Bagby