Tags

, , , ,

There was a question on Stack Overflow about “Oracle:how to reverse the string ‘ab,cd,ef’ to ‘ef->cd->ab’

Now, the question might look straight forward to simply reverse the string. However, it is not just reversing the complete string, but reversing the order of the indices. Like ‘WORD1, WORD2, WORD3’ to be reversed as ‘WORD3, WORD2, WORD1’

It could be done with a mix of string split and string aggregation.

Using:

  • REGEXP_SUBSTR : To split the comma delimited string into rows
  • LISTAGG : To aggregate the values

You can have a look at “Split comma delimited strings in a table using Oracle SQL” to understand how string split works.

Now, let’s see how to reverse the order of indices:

SQL> WITH DATA AS(
  2  SELECT 1 ID, 'word1,word2,word3' text FROM dual UNION ALL
  3  SELECT 2 ID, '1,2,3' text FROM dual UNION ALL
  4  SELECT 3 ID, 'a,b,c' text FROM dual
  5  )
  6  SELECT ID,
  7    listagg(text, ',') WITHIN GROUP (
  8  ORDER BY rn DESC) reversed_indices
  9  FROM
 10    (SELECT t.id,
 11      rownum rn,
 12      trim(regexp_substr(t.text, '[^,]+', 1, lines.COLUMN_VALUE)) text
 13    FROM data t,
 14      TABLE (CAST (MULTISET
 15      (SELECT LEVEL FROM dual CONNECT BY LEVEL <= regexp_count(t.text, ',')+1
 16      ) AS sys.odciNumberList ) ) lines
 17    ORDER BY ID
 18    )
 19  GROUP BY ID
 20  /

        ID REVERSED_INDICES
---------- ------------------------------
         1 word3,word2,word1
         2 3,2,1
         3 c,b,a

SQL>

Hope it helps!