HI All,
I have requirement when the values in my column is something like
"AB","XY","BG","DG","AC","GFD","GHT","BV","AC","EH","JH","HJ".
I need
Remove double quote
SORT the COMMA separated value alphabetically something like AB,AC,AC,BG,BV,DG,EH,GFD,GHT,HJ,JH
Current I am making 1 CTE to replace "" with blank
Then use REGEXP_SUBSTR to split comma separated values into ROWS
Then use LISTAGG to combine back into 1 single comma separated row by sorting
Just to sort i am doing Step 5 and 6 and it seems unnecessary to me to first split into multiple rows then combine back the same thing just for sorting
Kindly suggest a better way to avoid extra load in query
Sample Code:
create TABLE TEST
(
ID Number,
COMMENTS VARCHAR(4000)
);
INSERT INTO TEST (ID,COMMENTS)
VALUES (1,'"AB","XY","BG","DG","AC","GFD","GHT","AB","BV","AC","EH","JH","HJ"');
INSERT INTO TEST (ID,COMMENTS)
VALUES (1,'"ZX","YY","CG","DAG","AAC","GHFD","AA","AB","BU","CC","H","ZH","BJ"');
With REPLACE_DOUBLE_QUOTE as
( SELECT ID,COMMENTS,REPLACE(COMMENTS,'"','') CMT FROM TEST
)
,
SPLIT_INTO_ROWS AS -- comma separated values into rows so that in next pass I can use LISTAGG and SORT the VALUES
(
SELECT ID, REGEXP_SUBSTR(CMT,'[^,]+',1,level) SPLITED from REPLACE_DOUBLE_QUOTE
CONNECT BY LEVEL <= length(CMT)- LENGTH(replace(CMT,',',))+1
)
--- COMBINING multiple rows back in 1 row with COMMA Separation BUT BY SORTING
SELECT ID,LISTAGG(SPLITED ,)
LISTAGG(SPLITED ', ') WITHIN GROUP (ORDER BY SPLITED)
FROM SPLIT_INTO_ROWS
GROUP BY ID;