sqloracle-databaseoracle11gr2

How to arrange/sort string in Oracle SQL - 11g


I have a string an string "ADBDkK" and I need to sort it as "ABDDKk", like Arrays.sort() in java. I know it can be done by using PL/SQL but I need of this in Oracle SQL statement.

Input:

ADBDkK
ZXYABC

Output:

ABDDKk
ABCXYZ

Solution

  • SQL Fiddle

    Oracle 11g R2 Schema Setup:

    CREATE TABLE test ( value ) AS
    SELECT 'ADBDkK' FROM DUAL UNION ALL
    SELECT 'ZXYABC' FROM DUAL;
    

    Query 1:

    WITH chars ( id, value, ch, lvl ) AS (
      SELECT ROWNUM, value, SUBSTR( value, 1, 1 ), 1
      FROM   test
    UNION ALL
      SELECT id, value, SUBSTR( value, lvl+1, 1 ), lvl+1
      FROM   chars
      WHERE  lvl < LENGTH( value )
    )
    SELECT LISTAGG( ch ) WITHIN GROUP ( ORDER BY ch ) AS value
    FROM   chars
    GROUP BY id
    ORDER BY id
    

    Results:

    |  VALUE |
    |--------|
    | ABDDKk |
    | ABCXYZ |
    

    Query 2:

    SELECT LISTAGG( COLUMN_VALUE )
             WITHIN GROUP ( ORDER BY COLUMN_VALUE ) AS value
    FROM   (
      SELECT value,
             ROWNUM AS id
      FROM test
    ) t
    CROSS JOIN
    TABLE(
      CAST(
        MULTISET(
          SELECT SUBSTR( t.value, LEVEL, 1 )
          FROM   DUAL
          CONNECT BY LEVEL <= LENGTH( t.value )
        )
        AS SYS.ODCIVARCHAR2LIST
      )
    ) c
    GROUP BY t.id
    ORDER BY t.id
    

    Results:

    |  VALUE |
    |--------|
    | ABDDKk |
    | ABCXYZ |