How to Sort AlphaNumeric String based on the Number Order of the String in SQL Server

SQL Query to Sort AlphaNumeric String based on the Number Order in the String

Suppose we have a Table with Alpha Numeric data in Column 'NumStr' as follows..

Select * From #Tbl_AlphaNumStr

The above is data is not Sorted in any Order.

Now we will Sort the data in Ascending order, it looks as follows..
Select * From #Tbl_AlphaNumStr

Order By NumStr Asc

The above is a default Sorting format that SQL Server follows for AphaNumeric String. 

But this not the Correct Soring Order that we expected.

Our desired Sorting Order is 1,2,3,A1,A2..A10,A20..A100,AB1,AB2..AB100..B1,B2..B100.

We can achieve this using the below Query, where we sort data first by Alphabetical Order, then by Numerical Order as follows :

Select * From #Tbl_AlphaNumStr
-- Alphabetical Sort
Order By LEFT(NumStr,PATINDEX('%[0-9]%',NumStr)-1),
---- Numerical Sort
CONVERT(INT,SUBSTRING(NumStr,PATINDEX('%[0-9]%',NumStr),LEN(NumStr)))

Result :

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------