Friday, 10 March 2017

Sorting Alphanumeric values in Varchar2 column in Oracle SQL

Requirement: I have COURSES table having the values as shown below:

SELECT course_id, course_name, course_duration FROM courses;

When we see on the COURSE_ID column of the above output we can notice the values in the COURSE_ID column are not sorted.

Now to sort the COURSE_ID column in the ascending order we will click on the header of the COURSE_ID column. Thus the output is shown below. In the output we can still see the COURSE_ID are sorted considering the column as varchar.

Similarly is the case when we try to sort the COURSE_ID column in the descending order.

As per the requirement the table should be displayed as :

Ascending Order

Descending Order

Solution: For solution to the above requirement follow the steps as shown below:

Step 1: Create a table COURSES as shown below:
create table courses(course_id varchar2(20) primary key, course_name varchar2(30) not null,course_duration number(10) not null);

Step 2: Insert records in the COURSES table using the below INSERT statements:
insert into courses values('TR-01','Core Java',10);
insert into courses values('TR-02','C#',12);
insert into courses values('TR-04','C',15);
insert into courses values('TR-09','C++',16);
insert into courses values('TR-10','HTML',11);
insert into courses values('TR-03','CSS',10);
insert into courses values('TR-07','Java Script',3);
insert into courses values('TR-16','Oracle SQL',19);
insert into courses values('TR-08','MS Project',11);
insert into courses values('TR-05','ADF',13);
insert into courses values('TR-12','SOA',15);
insert into courses values('TR-15','Web Center Portal',16);
insert into courses values('TR-06','Struts',18);
insert into courses values('TR-14','Web Center  Site',12);
insert into courses values('TR-11','Hibernate',15);
insert into courses values('TR-13','Spring',10);
insert into courses values('TR-16','Oracle SQL',19);
insert into courses values('TR-108','Advanced MS Project',11);
insert into courses values('TR-105','Advanced ADF',13);
insert into courses values('TR-112','Advanced SOA',15);
insert into courses values('TR-115','Advanced Web Center Portal',16);
insert into courses values('TR-106','Advanced Struts',18);
insert into courses values('TR-114','Advanced Web Center  Site',12);
insert into courses values('TR-101','Advanced Hibernate',15);
insert into courses values('TR-103','Advanced Spring',10);

Step 3: Using the below statement to retrieve the expected output:
select
  case when REGEXP_LIKE (course.course_id, '[0-9]') then lpad(course.course_id,20) else course.course_id end as course_id,
  course_name,
  course_duration
from courses course;

Ascending Order: Click on the COURSE_ID column header to sort the table based on COURSE_ID ascending

Descending order: Click on the COURSE_ID column header to sort the table based on COURSE_ID descending

Hence the solution to our requirement.

If you like the post, then comment, share and do like me on Facebook.

Thanks & Regards,
Susanto Paul