Tuesday, June 11, 2013

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

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