0

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

1
select col_id, reverse(col_name) as col_name from tbl_test;

    COL_ID COL_NAME  
---------- ----------
         1 pantea    
         2 apple     
         3 banana    
  • Thanks . Did not know there is a function for this !! – Pantea Aug 24 '20 at 18:04
  • 1
    @Pani Keep in mind, this is an undocumented function. I did not know this and do not know why, I have just realized it when I wanted to add the reference to the documentation, but it is not in there, and saw other people mentioning it. You can see further solutions at: stackoverflow.com/questions/35314500/… – Balazs Papp Aug 24 '20 at 18:09
  • This is wired that the function has not been mentioned in any official oracle document !!! – Pantea Aug 24 '20 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.