在尝试按字母数字值排序时传递给LEFT或SUBSTRING函数的无效长度参数

Question

我试图排序一个列，有alpha数值，我发现这个代码在另一个帖子，当我执行它，我得到一个错误。

这是我的查询:

SELECT  
pmno, enrollno, membername, addr, photo, 
CAST(insdate AS DATE) AS reg_date 
FROM 
dbo.Member 
WHERE 
CAST(insdate as DATE) < '2020-01-20' 
AND court_name = 'City Court Unit'
ORDER BY 
LEFT(pmno, PATINDEX('%[0-9]%', pmno) - 1), -- alphabetical sort
CONVERT(INT, SUBSTRING(pmno, PATINDEX('%[0-9]%', pmno), LEN(pmno))) -- numerical

Answer 1

问题是PATINDEX对某些值返回0。

您可以使用NULLIF和ISNULL的组合来解决这个问题。

SELECT  
pmno, enrollno, membername, addr, photo, 
CAST(insdate AS DATE) AS reg_date 
FROM 
dbo.Member 
WHERE 
insdate < '2020-01-20' 
AND court_name = 'City Court Unit'
ORDER BY 
ISNULL(LEFT(pmno, NULLIF(PATINDEX('%[0-9]%', pmno), 0) - 1), pmno), -- alphabetical sort
CONVERT(INT, SUBSTRING(pmno, NULLIF(PATINDEX('%[0-9]%', pmno), 0), LEN(pmno)))

旁注:我强烈建议您不要使用CAST(insdate as DATE) < '2020-01-20'作为过滤器，因为它不会正确使用索引。改为使用insdate < '2020-01-20'