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.
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.
0 comments:
Post a Comment