How to arrange decimal numeric string in sequence using order by syntax in SQL

Requirement: 7 decimal numeric string required to arrange in numeric sequence.

Example:

2

2.1

2.1.1

2.1.2.3

2.1.2.3.4

2.1.2.3.4.5

2.1.2.3.4.5.6

2.1.2.3.4.5.6.7                         

Solution:

SQL Order BY syntax:

Note: Required to replace the ‘COLUMN_NAME’ as per your requirement and add into the SQL query.

order by

         to_number(substr( '.'||COLUMN_NAME||'.',

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 1 )+1,

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 2 )-instr( '.'||COLUMN_NAME||'.', '.', 1, 1 )-1 ))

            nulls first,

         to_number(substr( '.'||COLUMN_NAME||'.',

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 2 )+1,

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 3 )-instr( '.'||COLUMN_NAME||'.', '.', 1, 2 )-1 ))

            nulls first,

         to_number(substr( '.'||COLUMN_NAME||'.',

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 3 )+1,

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 4 )-instr( '.'||COLUMN_NAME||'.', '.', 1, 3 )-1 ))

            nulls first,

                                to_number(substr( '.'||COLUMN_NAME||'.',

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 4 )+1,

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 5 )-instr( '.'||COLUMN_NAME||'.', '.', 1, 4 )-1 ))

            nulls first,

                                                to_number(substr( '.'||COLUMN_NAME||'.',

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 5 )+1,

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 6 )-instr( '.'||COLUMN_NAME||'.', '.', 1, 5 )-1 ))

            nulls first,

                                                to_number(substr( '.'||COLUMN_NAME||'.',

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 6 )+1,

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 7 )-instr( '.'||COLUMN_NAME||'.', '.', 1, 6 )-1 ))

            nulls first,

                                                to_number(substr( '.'||COLUMN_NAME||'.',

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 7 )+1,

                   instr( '.'||COLUMN_NAME||'.', '.', 1, 8 )-instr( '.'||COLUMN_NAME||'.', '.', 1, 7 )-1 ))

            nulls first