SQL字母数字排序报错：LEFT/SUBSTRING长度参数无效解决方案问询

• 无数字的字段值会导致PATINDEX('%[0-9]%', DeptAbbreviation)返回0，传入LEFT的长度为0-1=-1，不符合函数参数要求，直接触发报错
• 数字后仍有字母的取值（如ST2D），原有逻辑会取出2D这类带非数字字符的子串，转INT时会报错

兼容无数字、数字夹在字符中间两类场景的SQL写法如下：

SELECT DeptAbbreviation 
FROM [JBSIS_Odyssey].[VW_Probate_Courthouse_Department] 
ORDER BY 
  -- 排序第一维度：字母前缀
  CASE 
    WHEN PATINDEX('%[0-9]%', DeptAbbreviation) = 0 THEN DeptAbbreviation
    ELSE LEFT(DeptAbbreviation, PATINDEX('%[0-9]%', DeptAbbreviation) - 1)
  END,
  -- 排序第二维度：连续数字部分
  CASE 
    WHEN PATINDEX('%[0-9]%', DeptAbbreviation) = 0 THEN 0
    ELSE CONVERT(INT, SUBSTRING(
      DeptAbbreviation,
      PATINDEX('%[0-9]%', DeptAbbreviation),
      PATINDEX('%[^0-9]%', SUBSTRING(DeptAbbreviation, PATINDEX('%[0-9]%', DeptAbbreviation), LEN(DeptAbbreviation)) + ' ') - 1
    ))
  END

逻辑说明 #

• 字母前缀部分：用CASE判断是否存在数字，无数字时直接用完整字段值作为排序依据，有数字时仅取第一个数字之前的字符
• 数字部分：无数字时默认赋值为0，避免排序错乱；有数字时先定位第一个数字的位置，再匹配第一个数字之后第一个非数字的位置，截取中间连续的数字串转INT，自动兼容数字后带字母的场景；末尾拼接空格是为了避免数字在字段末尾时找不到非数字字符的边界问题

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