Sort by Number and Alphabets in SQL Server

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 183
Let's say we have a table of records as under

Record
------
01A1
02A1
18A1
20A1
17A1
19A1
01B1
02B1
20B1
17B1
18B1
19B1


We need the reocrds to be sorted such that the final output should appear as

Record
---------
01A1
01B1
02A1
02B1
17A1
17B1
18A1
18B1
19A1
19B1
20A1
20B1


The below query will do so

DECLARE @T TABLE(Record VARCHAR(20))

INSERT INTO @T

SELECT '01A1' UNION ALL
SELECT '02A1' UNION ALL
SELECT '18A1' UNION ALL
SELECT '20A1' UNION ALL
SELECT '17A1' UNION ALL
SELECT '19A1' UNION ALL
SELECT '01B1' UNION ALL
SELECT '02B1' UNION ALL
SELECT '20B1' UNION ALL
SELECT '17B1' UNION ALL
SELECT '18B1' UNION ALL
SELECT '19B1'

SELECT *
FROM @T
ORDER BY
CAST(SUBSTRING(Record, PATINDEX('%[0-9]%', Record), PATINDEX('%[0-9][^0-9]%', Record + 't') - PATINDEX('%[0-9]%', Record) + 1) AS INT)
,LEFT(REVERSE(SUBSTRING(Record, PATINDEX('%[0-9]%', Record), PATINDEX('%[0-9][^0-9]%', Record + 't') - PATINDEX('%[0-9]%', Record) + 2)),1)


Here we are first sorting by numbers and then by Alphabets

Comments or Responses

Login to post response