I have a table with below structure:
create table TBL_TEST
(
col_id NUMBER,
col_name VARCHAR2(500)
)
Some example data :
col_id | col_name
-----------------
1 | aetnap
2 | elppa
3 | ananab
What I want to do is to reverse
the value of col_name
column like this :
col_id | col_name
-----------------
1 | pantea
2 | apple
3 | banana
I used listagg and regexp_replace
to write the query:
select col_id,
listagg(val) within group(order by val_row desc) original_value
from (select col_id,
val,
row_number() over(partition by col_id order by col_id) val_row
from (select col_id, trim(column_value) val
from tbl_test,
xmltable(trim(trailing ',' from
regexp_replace(col_name, '(.)', '"\1",')))))
group by col_id;
The query above works fine and gives me the desired result, I want to know if there are better ways of writing the query cause two sub queries are used in the query above and I want to know whether there are better ways than using a sub query.
Thanks in advance
Best Answer