Alpha-Numeric Sorting in SQL Server

Introduction

Let's say we have a table as under

which is generated by using the below TSQL query

DECLARE @T TABLE(Record VARCHAR(20))

--Populate some values
INSERT INTO @T 
SELECT '01A1001' UNION ALL 
SELECT '02A1001' UNION ALL
SELECT '03A1001' UNION ALL
SELECT '04A1001' UNION ALL
SELECT '15A1001' UNION ALL 
SELECT '18A1001' UNION ALL 
SELECT '20A1001' UNION ALL
SELECT '17A1001' UNION ALL 
SELECT '12A1001' UNION ALL
SELECT '13A1001' UNION ALL
SELECT '14A1001' UNION ALL
SELECT '16A1001' UNION ALL 
SELECT '05A1001' UNION ALL
SELECT '06A1001' UNION ALL
SELECT '07A1001' UNION ALL
SELECT '08A1001' UNION ALL
SELECT '09A1001' UNION ALL
SELECT '10A1001' UNION ALL
SELECT '11A1001' UNION ALL
SELECT '19A1001' UNION ALL 
SELECT '01B1001' UNION ALL 
SELECT '02B1001' UNION ALL
SELECT '03B1001' UNION ALL
SELECT '04B1001' UNION ALL
SELECT '15B1001' UNION ALL 
SELECT '10B1001' UNION ALL
SELECT '20B1001' UNION ALL
SELECT '12B1001' UNION ALL
SELECT '14B1001' UNION ALL
SELECT '13B1001' UNION ALL
SELECT '05B1001' UNION ALL
SELECT '06B1001' UNION ALL
SELECT '17B1001' UNION ALL 
SELECT '07B1001' UNION ALL
SELECT '08B1001' UNION ALL
SELECT '18B1001' UNION ALL
SELECT '16B1001' UNION ALL 
SELECT '09B1001' UNION ALL
SELECT '11B1001' UNION ALL
SELECT '19B1001'

--Project the records
SELECT *
FROM @T

The objective is to sort the "Record" field in such a way so that the final output resemble

If we keenly observe the problem, we can understand that, in this case first we need to perform a numeric sorting first followed by their alphabets. In this article will discuss an approach for the same.

Step by Step Approach for the problem

Let us first extract the number by writing the below T-SQL code

SELECT * , ExtractNumbers = SUBSTRING(Record, 1,PATINDEX('%[A-Za-z]%', Record)-1)
FROM @T

PATINDEX('%[A-Za-z]%', Record) returns the first occurance of the alphabets. For example if we have a string as "0678Az34", the result will be

SELECT FirstLetterPosition = PATINDEX('%[A-Za-z]%', '0678Az34')

/*Result*/
-----------
FirstLetterPosition
5

Subtracting 1 from the above result and applying a substring will yield

SELECT 
	FirstLetterPosition = PATINDEX('%[A-Za-z]%', '0678Az34')
	,NumbersBeforFirstLetter = SUBSTRING('0678Az34', 1,PATINDEX('%[A-Za-z]%', '0678Az34')-1)

/*Result*/
-----------
FirstLetterPosition	NumbersBeforFirstLetter
5			0678

We need to apply this trick first.

SELECT 
	* 	
	,ExtractNumbers = SUBSTRING(Record, 1,PATINDEX('%[A-Za-z]%', Record)-1)
FROM @T

/*Result*/
-----------
Record	ExtractNumbers
01A1001	01
02A1001	02
03A1001	03
04A1001	04
15A1001	15
18A1001	18
20A1001	20
17A1001	17
12A1001	12
13A1001	13
14A1001	14
16A1001	16
05A1001	05
06A1001	06
07A1001	07
08A1001	08
09A1001	09
10A1001	10
11A1001	11
19A1001	19
01B1001	01
02B1001	02
03B1001	03
04B1001	04
15B1001	15
10B1001	10
20B1001	20
12B1001	12
14B1001	14
13B1001	13
05B1001	05
06B1001	06
17B1001	17
07B1001	07
08B1001	08
18B1001	18
16B1001	16
09B1001	09
11B1001	11
19B1001	19

The very next step will be to extract the first alphabet from the string . For example,if we have a string as "0678Az34", and if we need to extract the very first alphabet from the string i.e. 'A', the query will be

SELECT 
	ExtractFirstAlphabet = SUBSTRING('0678Az34', PATINDEX('%[A-Za-z]%', '0678Az34'),1)

/*Result*/
ExtractFirstAlphabet
A

We need to apply the same trick as under

SELECT 
	* 
	,ExtractFirstAlphabet = SUBSTRING(Record, PATINDEX('%[A-Za-z]%', Record),1)
FROM @T

/*Result*/
Record	ExtractFirstAlphabet
01A1001	A
02A1001	A
03A1001	A
04A1001	A
15A1001	A
18A1001	A
20A1001	A
17A1001	A
12A1001	A
13A1001	A
14A1001	A
16A1001	A
05A1001	A
06A1001	A
07A1001	A
08A1001	A
09A1001	A
10A1001	A
11A1001	A
19A1001	A
01B1001	B
02B1001	B
03B1001	B
04B1001	B
15B1001	B
10B1001	B
20B1001	B
12B1001	B
14B1001	B
13B1001	B
05B1001	B
06B1001	B
17B1001	B
07B1001	B
08B1001	B
18B1001	B
16B1001	B
09B1001	B
11B1001	B
19B1001	B

Finally we need to apply this two logic in the "Order By" clause in order to achieve the desire result

SELECT 
	* 
FROM @T
ORDER BY
	CAST(SUBSTRING(Record, 1,PATINDEX('%[A-Za-z]%', Record)-1) AS INT) --First numeric sorting
	,SUBSTRING(Record, PATINDEX('%[A-Za-z]%', Record),1) --Sort by Alphabets

Result

If in case we need to perform a reverse of this, we can use

--Project the records
SELECT 
	* 
FROM @T
ORDER BY
	CAST(SUBSTRING(Record, 1,PATINDEX('%[A-Za-z]%', Record)-1) AS INT) DESC --First numeric sorting
	,SUBSTRING(Record, PATINDEX('%[A-Za-z]%', Record),1) --Sort by Alphabets

Result

Conclusion

In this article we have learnt about Alpha-Numeric Sorting in SQL Server . Hope this will be helpful. Thanks for reading. Zipped file attached.