How to reverse a string in Oracle (11g) SQL without using REVERSE() function

Question

I am trying to reverse a string without using REVERSE function. I came across one example which is something like:

select listagg(letter) within group(order by lvl)
from 
 (SELECT LEVEL lvl, SUBSTR ('hello', LEVEL*-1, 1) letter 
 FROM   dual 
 CONNECT BY LEVEL <= length('hello'));

Apart from this approach,is there any other better approach to do this?

Answer 1

If you're trying to avoid the undocumented reverse() function you could use the utl_raw.reverse() function instead, with appropriate conversion too and from RAW:

select utl_i18n.raw_to_char(
  utl_raw.reverse(
    utl_i18n.string_to_raw('Some string', 'AL32UTF8')), 'AL32UTF8')
from dual;

UTL_I18N.RAW_TO_CHAR(UTL_RAW.REVERSE(UTL_I18N.STRING_TO_RAW('SOMESTRING','AL32UT
--------------------------------------------------------------------------------
gnirts emoS                                                                     

So that is taking an original value; doing utl_i18n.string_to_raw() on that; then passing that to utl_raw.reverse(); then passing the result of that back through utl_i18n.raw_to_char().

Not entirely sure how that will cope with multibyte characters, or what you'd want to happen to those anyway...

Or a variation from the discussion @RahulTripathi linked to, without the character set handling:

select utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw('Some string')))
from dual;

UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW('SOMESTRING')))   
--------------------------------------------------------------------------------
gnirts emoS                                                                     

But that thread also notes it only works for single-byte characters.