Ads Inside Post

Search in This Blog

MS Access alphanumeric column sorting as number

Alphanumric column sorting as numeric in MS Access

You have Ms Access Database table as listed belwo:
------------------------------------------
id,p_num, city,branch
------------------------------------------ 
1,P1,a,b
2,P9,c,d
3,P19,e,f
4,P2,g,h
5,P111,i,j
6,P100,k.l
7,P10,m,n
8,P7,o,p
------------------------------------------

Now you want to show the record by "p_num" column, you can see "p_num" is a alphanumeric value and you want to sort is as below:
------------------------------------------ 
id,p_num, city,branch
------------------------------------------ 
1,P1,a,b
2,P2,g,h
3,P7,o,p
4,P9,c,d
5,P10,m,n
6,P19,e,f
7,P100,k.l
8,P111,i,j
------------------------------------------
So in this case use this sql:

SELECT id,Mid(p_num,2,12) AS new_p_num,city,branch FROM my_tbl order by CInt(Mid(p_num,2,12) )

In the above sql query "Mid(p_num,2,12)" 2 is the starting position of p_num column and 12 is the maximum digits. Here I am assuming after "P" number is not more that 12 digits.