MS SQL Sort Alphanumeric values (1, 2, 3, 5, 6, 7, 9, 10, 4a, 8a, 4b, 8b)

April 16, 2023

In SQL database (2005/2008), I have a field named Ordernumber having the following values: 1, 2, 3, 5, 6, 7, 9, 10, 4a, 8a, 4b, 8b I want a way to sort them to be 1, 2, 3, 4a, 4b, 5, 6, 7, 8a, 8b, 9, 10. How to do?

I used the following:

SELECT Ordernumber
FROM alphanumericorder
ORDER BY
  CASE
    WHEN ISNUMERIC(Ordernumber) = 1 THEN CAST(Ordernumber AS INT)
    ELSE CAST(SUBSTRING(Ordernumber, 1, PATINDEX('%[0-9]%', Ordernumber) - 1) AS VARCHAR(100)) 
      + RIGHT('0000000000' + CAST(SUBSTRING(Ordernumber, PATINDEX('%[0-9]%', Ordernumber), LEN(Ordernumber)) AS VARCHAR(100)), 10)
  END

but I got the following error: "Conversion failed when converting the varchar value ‘000000004a’ to data type int"

Then I used:

  SELECT Ordernumber
FROM alphanumericorder
ORDER BY
  CASE
    WHEN ISNUMERIC(LEFT(Ordernumber,1)) = 1 THEN CAST(Ordernumber AS INT)
    ELSE CAST(LEFT(Ordernumber,LEN(Ordernumber)-1) AS VARCHAR(100)) 
      + RIGHT('0000000000' + CAST(RIGHT(Ordernumber,1) AS VARCHAR(100)), 1)
  END;

and I got: "Conversion failed when converting the nvarchar value ‘4a’ to data type int"

Please advise.

>Solution :

How about something like this:

select *
from (
    VALUES  (N'1')
    ,   (N'2')
    ,   (N'3')
    ,   (N'5')
    ,   (N'6')
    ,   (N'7')
    ,   (N'9')
    ,   (N'10')
    ,   (N'4a')
    ,   (N'8a')
    ,   (N'4b')
    ,   (N'8b')
) t ([Ordernumber])
order by CASE
    WHEN ISNUMERIC(Ordernumber) = 1 THEN CAST(Ordernumber AS INT)
    ELSE CAST(SUBSTRING(Ordernumber, 1, PATINDEX('%[^0-9]%', Ordernumber)-1) AS INT)
  END, OrderNumber

This converts the number part of the ordernumber by stripping everything from first non-numbered digit