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.


Previous
Next Post »