Sort Alphanumeric Data in Real Number in Sql

Sort Alphanumeric Data in Real Number in Sql


Suppose you have a table with columns pID Autonumber, registrationNo varchar(50) ,pName varchar(100)
registrationNo value is the first character is alphabets and rest is the number listed below:


pID registrationNo
1,    A1315
2,    A1
3,    A132
4,    A2
5,    A1316
6,    A1317
7,    A1318
8,    A133
9,    A1330
10,   A1331
11,   A3
12,   A1450
13,   A4
14,   A5643
15,   A5
16,   A1390
17,   A6

My client want to see those records in ascending number order as listed below order:
pID registrationNo
2,    A1
4,    A2
11,   A3
13,   A4
15,   A5
17,   A6
3,    A132
8,    A133
9,    A1330
10,   A1331
1,    A1315
5,    A1316
6,    A1317
7,    A1318
16,   A1390
12,   A1450
14,   A5643

but when retrieve the order by registrationNo. records was coming in ascending order,
because registrationNo are not a integer data type , those are Alphanumeric data type.

So how to show the Alphanumeric data in real ascending number order?
I have solved this problem with using listed below Sql. listed below sql will show the records which start with character 'a' :

SELECT pID,CAST(SUBSTRING(registrationNo, 2, 10) AS INT) as registrationNoNew
FROM [patientdb].[dbo].[Patients] where registrationNo like '%a1%' order by registrationNoNew