CODEWITHSUNDEEP
oracle-databaseoracle11g
user5355231
user5355231

Reputation: 33

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

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?

Upvotes: 3

Views: 21508

Answers (5)

Ratha
Ratha

Reputation: 1

select listagg(rev)within group(order by rownum)
from
(select substr('Oracle',level*-1,1)rev from dual
connect by level<=length('Oracle'));

Upvotes: 0

Vaas
Vaas

Reputation: 67

SELECT LISTAGG(STR) WITHIN GROUP (ORDER BY RN DESC)
FROM 
(
     SELECT ROWNUM RN, SUBSTR('ORACLE',ROWNUM,1) STR FROM DUAL 
     CONNECT BY LEVEL <= LENGTH('ORACLE')
);

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191295

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.

Upvotes: 3

Boneist
Boneist

Reputation: 23588

You could do it like this:

with strings as (select 'hello' str from dual union all
                 select 'fred' str from dual union all
                 select 'this is a sentance.' from dual)
select str,
       replace(sys_connect_by_path(substr (str, level*-1, 1), '~|'), '~|') rev_str 
from   strings
where  connect_by_isleaf = 1
connect by prior str = str                  --added because of running against several strings at once
           and prior sys_guid() is not null --added because of running against several strings at once 
           and level <= length(str);

STR                 REV_STR             
------------------- --------------------
fred                derf                
hello               olleh               
this is a sentance. .ecnatnes a si siht

N.B. I used a delimiter of ~| simply because that's something unlikely to be part of your string. You need to supply a non-null delimiter to the sys_connect_by_path, hence why I didn't just leave it blank!

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

You can try using this function:

SQL> ed
Wrote file afiedt.buf

  1  with t as (select 'Reverse' as txt from dual)
  2  select replace(sys_connect_by_path(ch,'|'),'|') as reversed_string
  3  from (
  4        select length(txt)-rownum as rn, substr(txt,rownum,1) ch
  5        from t
  6        connect by rownum <= length(txt)
  7       )
  8  where connect_by_isleaf = 1
  9  connect by rn = prior rn + 1
 10* start with rn = 0
SQL> /

Source

Upvotes: 0

Related Questions