OrderBy on a varchar in oracle

Question

Below are the column data(ORDER) of one of my table (MyTABLE),

a
a.1
a.1.1
a.2.0
a.1.10
a.1.4
a.20.1
a.2.2.1
a.4.3.1
a.4
a.40

I want to order it in the below way,

a
a.1
a.1.1
a.1.4
a.2.0
a.2.2.1
a.4
a.4.3.1
a.20.1
a.40
b.*....(if any data present which has b prefixed etc.,)

I would be wondering if this can be achieved at database level itself while fetching results? If not then how would we do it in java?

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE MyTable ( name ) AS
          SELECT 'a' FROM DUAL
UNION ALL SELECT 'a.1.4' FROM DUAL
UNION ALL SELECT 'a.2.2.1' FROM DUAL
UNION ALL SELECT 'a.1.1' FROM DUAL
UNION ALL SELECT 'a.1' FROM DUAL
UNION ALL SELECT 'a.40' FROM DUAL
UNION ALL SELECT 'a.4' FROM DUAL
UNION ALL SELECT 'a.4.3.1' FROM DUAL
UNION ALL SELECT 'a.20.1' FROM DUAL
UNION ALL SELECT 'b.1' FROM DUAL
UNION ALL SELECT 'a.2.0' FROM DUAL

Query 1:

SELECT *
FROM   MyTable
ORDER BY
       REGEXP_SUBSTR( name, '[^.]+', 1, 1 ),
       TO_NUMBER( REGEXP_SUBSTR( name, '[^.]+', 1, 2 ) ) NULLS FIRST,
       TO_NUMBER( REGEXP_SUBSTR( name, '[^.]+', 1, 3 ) ) NULLS FIRST,
       TO_NUMBER( REGEXP_SUBSTR( name, '[^.]+', 1, 4 ) ) NULLS FIRST

Results:

|    NAME |
|---------|
|       a |
|     a.1 |
|   a.1.1 |
|   a.1.4 |
|   a.2.0 |
| a.2.2.1 |
|     a.4 |
| a.4.3.1 |
|  a.20.1 |
|    a.40 |
|     b.1 |

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE MyTable ( name VARCHAR(20) );

INSERT INTO MyTable
          SELECT 'a' FROM DUAL
UNION ALL SELECT 'a.1.4' FROM DUAL
UNION ALL SELECT 'a.2.2.1' FROM DUAL
UNION ALL SELECT 'a.1.1' FROM DUAL
UNION ALL SELECT 'a.1' FROM DUAL
UNION ALL SELECT 'a.40' FROM DUAL
UNION ALL SELECT 'a.4' FROM DUAL
UNION ALL SELECT 'a.4.3.1' FROM DUAL
UNION ALL SELECT 'a.20.1' FROM DUAL
UNION ALL SELECT 'b.1' FROM DUAL
UNION ALL SELECT 'a.2.0' FROM DUAL;

Query 1:

SELECT name
FROM   MyTable
ORDER BY
       SUBSTRING_INDEX( name, '.', 1 ),
       CASE WHEN SUBSTRING_INDEX( name, '.', 2 ) = SUBSTRING_INDEX( name, '.', 1 ) THEN -1 ELSE CAST( SUBSTRING_INDEX( SUBSTRING_INDEX( name, '.', 2 ), '.', -1 ) AS SIGNED ) END,
       CASE WHEN SUBSTRING_INDEX( name, '.', 3 ) = SUBSTRING_INDEX( name, '.', 2 ) THEN -1 ELSE CAST( SUBSTRING_INDEX( SUBSTRING_INDEX( name, '.', 3 ), '.', -1 ) AS SIGNED ) END,
       CASE WHEN SUBSTRING_INDEX( name, '.', 4 ) = SUBSTRING_INDEX( name, '.', 3 ) THEN -1 ELSE CAST( SUBSTRING_INDEX( SUBSTRING_INDEX( name, '.', 4 ), '.', -1 ) AS SIGNED ) END

Results:

|    name |
|---------|
|       a |
|     a.1 |
|   a.1.1 |
|   a.1.4 |
|   a.2.0 |
| a.2.2.1 |
|     a.4 |
| a.4.3.1 |
|  a.20.1 |
|    a.40 |
|     b.1 |

Answer 2

It is possible but suggest you to go this way only if you know your data well and can handle number format exception.

SELECT *
FROM TABLE
ORDER BY 
TO_NUMBER(SUBSTR( REPLACE(column,'a.'),1, 
        INSTR(REPLACE(column,'a.'),'.')
      ) ||
SUBSTR( REPLACE(column,'a.'), 
        INSTR(REPLACE(column,'a.'),'.'),
        LENGTH(REPLACE(column,'a.'))
      ))

How does this order by work:

• Get rid of 'a.' from column value
• Fetch first number after 'a.' which would be like 1, 2 , 4
• In the remaining string after 1., 2. remove all '.'
• finally your data should come to this numerical form 1.0, 1.11, 2.0, 2.21, 4.0, 4.31

Once you get your data in above numerical form, the order will work as you expecting.

Again this is very specific scenario, your data may be more patterns than this.