Write a Query to reverse the string without reverse function

Input: ORACLE

Output: ELCARO

This example shows how to split a string into characters and then reverse it using CONNECT BY and LISTAGG.

STEP 1

with t as (select 'ORACLE' d from dual)
select d from t connect by level <= length(d);

Output:

ORACLE
ORACLE
ORACLE
ORACLE
ORACLE
ORACLE

STEP 2

with t as (select 'ORACLE' d from dual)
select d, substr(d, level, 1) from t connect by level <= length(d);

Output:

ORACLE    O
ORACLE    R
ORACLE    A
ORACLE    C
ORACLE    L
ORACLE    E

STEP 3

with t as (select 'ORACLE' d from dual)
select d, substr(d, level, 1), level from t connect by level <= length(d);

Output:

ORACLE    O    1
ORACLE    R    2
ORACLE    A    3
ORACLE    C    4
ORACLE    L    5
ORACLE    E    6

STEP 4

with t as (select 'ORACLE' d from dual)
select listagg(s) within group (order by l) col from
(
  select d, substr(d, level, 1) s, level l
  from t
  connect by level <= length(d)
);

Output:

ORACLE

STEP 5

with t as (select 'ORACLE' d from dual)
select listagg(s) within group (order by l desc) col from
(
  select d, substr(d, level, 1) s, level l
  from t
  connect by level <= length(d)
);

Output:

ELCARO

This is a simple and elegant way to reverse a string in Oracle SQL.