Tuesday, June 11, 2013

Sort Characters (of datatype char, varchar) in order of month name

When we generally do order by .. when it is string or varchar/nvarchar datatype then oracle or any db server will do order in way, that occurance of charater is priority.

for example :
A, B, C, D, E

In case if integer numbers are saved in Varchar database type then it will do the order as below:
1, 10, 11, 2, 22, 3, 4, 5, 55


In case if integer numbers are saved in Int database type then it will do the order as below:
1, 2, 3, 4, 5, 10, 11, 22, 55

Create table having datatype varchar2 or any of yours choice:

create table month_sort (mon1 varchar2(3));
insert into month_sort values ('MAR');
insert into month_sort values ('JUN');
insert into month_sort values ('MAY');
insert into month_sort values ('FEB');
insert into month_sort values ('JAN');
insert into month_sort values ('DEC');
insert into month_sort values ('SEP');
insert into month_sort values ('OCT');




::: this is a character sorting
select * from month_sort order by 1;

::: this is a character sorting in order of month name
select mon1 ,row_number() over (partition by  to_date(mon1,'mon') order by  to_date(mon1,'mon')) from month_sort;

No comments:

Post a Comment

web stats