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
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
| 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
| VALUE |
|--------|
| ABDDKk |
| ABCXYZ |