[SOLVED] Reverse order of split string parts

Reverse order of split string parts

My simple Oracle SQL select statement:

select id, my_column
from my_table;

returns the following output with over 1.000.000 rows in the result set:

| id  | my_column   |
| 1   | abc.mno.xyz |
| 2   | ab.cd       |
| 3   | abcde       |
| ... | ...         |

But I need the output to look like this:

| id  | my_column   |
| 1   | xyz.mno.abc |
| 2   | cd.ab       |
| 3   | abcde       |
| ... | ...         |

How can I reverse the order (of the string values in my_column) of the with "." split parts, but not the order in the split parts in a performant way?

Thank you

Solution

For 11gR2, https://dbfiddle.uk/OJkJlpva

with data(id, mycolumn) as (
    select 1, 'abc.mno.xyz' from dual union all
    select 2, 'ab.cd'  from dual union all
    select 3, 'abcde' from dual
)
select id, mycolumn, substr(revmycolumn, 2) as revmycolumn
from data d,
xmltable(
  'string-join (
    for $i at $idx in ora:tokenize($X,"\.") order by $idx descending return( $i ), "."
    )'
  passing mycolumn || '.' as "X"
  columns 
    revmycolumn varchar2(4000) path '.'
  ) t
order by id
;

For 23c: https://dbfiddle.uk/4R13zNiF

with data(id, mycolumn) as (
    select * from (
        values
            (1, 'abc.mno.xyz'),
            (2, 'ab.cd'),
            (3, 'abcde')
        ) as t(id, mycolumn)
)
select id, mycolumn, revmycolumn
from data d,
xmltable(
  'string-join (
    for $i at $idx in fn:tokenize($X,"\.") order by $idx descending return( $i ), "."
    )'
  passing mycolumn as "X"
  columns 
    revmycolumn varchar2(4000) path '.'
  ) t
order by id
;