Invalid length parameter passed to the LEFT or SUBSTRING function while trying to sort by alphanumeric value

Question

I'm trying to sort a column that has alpha numeric values, I found this code on another post and when I execute it I'm getting an error.

This is my query:

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

The problem is that PATINDEX is returning 0 for some values.

You can use a combination of NULLIF and ISNULL to worka round that.

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)))

Side note: I strongly suggest you do not use CAST(insdate as DATE) < '2020-01-20' as a filter, as it will not use indexing properly. Instead use insdate < '2020-01-20'