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
3 Answers
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 |
4 Comments
MT0
What do you mean? This is sorting it.
MT0
Write a PL/SQL function to perform the sort - but you said you didn't want that in the question.
For a single string:
select listagg(regexp_substr('ADBDkK', '\w', 1 ,level),'')
within group (order by 1) from dual
connect by regexp_substr('ADBDkK', '\w', 1 ,level) is not null;
A slightly different way using a function:
CREATE OR REPLACE FUNCTION sort_string(my_string IN VARCHAR2)
RETURN VARCHAR2 IS
ret_string VARCHAR2(4000);
BEGIN
SELECT LISTAGG(regexp_substr(my_string, '\w', 1, level), '') WITHIN
GROUP(
ORDER BY 1)
INTO ret_string
FROM dual
CONNECT BY regexp_substr(my_string, '\w', 1, level) IS NOT NULL;
RETURN ret_string;
END;
Then from sqlplus:
SQL> select sort_string('ADBDkK') as RESULT from dual;
RESULT
------
ABDDKk
SQL> select sort_string('ZXYABC') as RESULT from dual;
RESULT
------
ABCXYZ
answered May 9, 2018 at 7:27
Juan Diego Godoy Robles
15k2 gold badges43 silver badges57 bronze badges
4 Comments
MT0
This will only work for a single string - if you try this on a table with multiple rows then the hierarchical query cannot correlate the hierarchy to a single table row and will start to generate exponentially more duplicate rows as the hierarchy deepens.
Juan Diego Godoy Robles
That's correct, It must be integrated in a function
MT0
You don't need a function; you can also correlate it using a
MULTISET statement (as the 2nd query in my answer). Also, why use REGEXP_SUBSTR and not just SUBSTR?Juan Diego Godoy Robles
Not saying I need a
function this is just an alternative way to do it, your answer is great BTWwith t1 as (
select 'London Singapur' tmp from dual union all
select 'Singapur China' tmp from dual union all
select 'USA JAPAN ' tmp from dual union all
select 'JAPAN USA' tmp from dual union all
select 'Singapur London' tmp from dual
),
dst as ( select ROWNUM rwn,tmp,REGEXP_COUNT(tmp,'[^[:space:]]+') cnt_array from t1 ),
rc(id, cnt_id, tmp, CNT_ARRAY, SL) AS (
SELECT rwn id,1 cnt_id,tmp, CNT_ARRAY, regexp_substr(tmp,'[^[:space:]]+',1,1) sl FROM dst
UNION ALL
SELECT rc.id, rc.cnt_id+1 cnt_id, rc.tmp, rc.CNT_ARRAY, regexp_substr(rc.tmp,'[^[:space:]]+',1,rc.cnt_id+1) sl
FROM dst tr, rc
WHERE tr.rwn = rc.id and rc.cnt_id+1<=tr.CNT_ARRAY
),
srt as (select rc.*,row_number() over (partition by id order by id) nb,
listagg(sl,' ') WITHIN GROUP (ORDER BY sl) over(partition by id) fiel_srt
from rc)
select * from srt where nb=1
