For smart Primates & ROBOTS (oh and ALIENS ).

Blogroll

Sunday, September 14, 2014

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.


Share:

0 comments:

Post a Comment

Multiple attribute passing in querySelectorAll

Multiple attribute passing in querySelectorAll     Here I am demonstrating code to how to pass multiple attributes in querySelectorAll. <...

Ads Inside Post

Powered by Blogger.

Arsip

Blog Archive