SQL Puzzle - Sorting Versions stored in Varchar2 Column

I am a regular follower of ASKTOM and OTN. I found a interesting question regarding Sorting the Versions which I thought could be a very good candidate to be on this Puzzle series. I am hoping that you will also like it and will enjoy solving it.

Lets first create the table and data for this.
SQL> create table versions
  2  (
  3    version varchar2(30)
  4  );
Table created.

SQL> insert into versions values ('1.1');
1 row created.

SQL> insert into versions values ('1.1.1');
1 row created.

SQL> insert into versions values ('1.2.10');
1 row created.

SQL> insert into versions values ('1');
1 row created.

SQL> insert into versions values ('1.10.1');
1 row created.

SQL> insert into versions values ('1.1.2');
1 row created.

SQL> insert into versions values ('2.1');
1 row created.

SQL> insert into versions values ('2');
1 row created.

SQL> insert into versions values ('1.10.2');
1 row created.

SQL> insert into versions values ('1.2');
1 row created.

SQL> insert into versions values ('1.2.4');
1 row created.

SQL> insert into versions values ('1.2.5');
1 row created.

SQL> commit;
Commit complete.

Here goes the problem Statement:
I want to sort data of versions table by individual number separated by Dot of version column. below is the result I need:
1 
1.1 
1.1.1 
1.1.2 
1.2 
1.2.4 
1.2.5 
1.2.10 
1.10.1 
1.10.2 
2
2.1

Here is the solution which I have created
  SELECT version
    FROM versions
ORDER BY TO_NUMBER (REGEXP_SUBSTR (version, '\d+')),
         TO_NUMBER (REGEXP_SUBSTR (version,'\d+',1,2)) NULLS FIRST,
         TO_NUMBER (REGEXP_SUBSTR (version,'\d+',1,3)) NULLS FIRST;


You can put your answers on the comment box. Enjoy solving it :)

More SQL Puzzles:
- SQL Puzzle - Transpose Rows and Shift Values among columns
- Graph Shortest Path Solution by SQL
- Diamond Shaped Star Pattern by SQL
- SQL Puzzle - Grouping Deals
- SQL Puzzle - Consecutive Wins
- SQL Puzzle - Issue Tracker
- SQL Interview Question Answers

3 comments:

  1. order by to_number(regexp_substr(version, '[[:digit:]]+', 1, 1)) nulls first,
    to_number(regexp_substr(version, '[[:digit:]]+', 1, 2)) nulls first,
    to_number(regexp_substr(version, '[[:digit:]]+', 1, 3)) nulls first

    Edgaras Žuklys
    DBA at LB

    ReplyDelete
  2. order by regexp_replace(regexp_replace(version,'([[:digit:]]+)','00001'),'0*([[:digit:]]{3})','1')

    ReplyDelete
  3. In the old days before REGEXP it could still be done.But it was a pain!

    ORDER BY
    CASE WHEN INSTR(version,'.') = 0 THEN TO_NUMBER(version)
    ELSE TO_NUMBER(SUBSTR(version,1,INSTR(version,'.',1,1)-1)) END
    ,CASE WHEN INSTR(version,'.',1,1) = 0 THEN 0
    ELSE TO_NUMBER(SUBSTR(version,INSTR(version,'.',1,1)+1,INSTR(version,'.',1,2)-INSTR(version,'.',1,1)-1))
    END
    ,CASE WHEN INSTR(version,'.',1,2) = 0 THEN 0
    ELSE TO_NUMBER(SUBSTR(version,INSTR(version,'.',1,2)+1))
    END

    ReplyDelete