There are undocument functions in Oracle SQL that implement the functionality of reverse string, so can it be implemented in other ways? Here are a few ways to:
1.undocument REVERSE FUNCTION
SELECT REVERSE (' Test REVERSE ') from dual;
--Note that Chinese may be garbled
SELECT REVERSE (' Test REVERSE ') from dual;
--2 times reverse Normal
SELECT Reverse (reverse (' Test reverse ')) from dual;
2. Use Utl_raw. REVERSE
SELECT utl_raw.cast_to_varchar2 (Utl_raw.reverse (utl_raw.cast_to_raw (' Test reverse ')) from dual;
--Chinese ditto
3. Recursive SQL to achieve reverse, the benefits of Chinese can be directly reverse
--delimited, select special symbol with no strings
With T as
(SELECT ' test reverse ' as STR from dual)
SELECT REPLACE (Sys_connect_by_path (res_str, ' | '), ' | ') As Reversed_string
From (SELECT length (str)-rownum as RN, substr (str, rownum, 1) res_str
From T
CONNECT by rownum <= Length (str))
WHERE connect_by_isleaf = 1
CONNECT by rn = PRIOR RN + 1
START with rn = 0;
--11g R2 recursive with
With T (str, S, c)
As (
SELECT ' Test reverse ' str, CAST (NULL as VARCHAR2) s, 0
From dual
UNION All
SELECT str, s | | substr (str,-c-1, 1), C + 1 from t WHERE C <= Length (str)
)
SELECT max (str) STR, MAX (s) rev_str from T;
4. Use undocument wm_concat OR 11g R2 Listagg, the benefits of Chinese can be directly reverse
SELECT REPLACE (Wm_concat (NAME), ', ', ', ') rev_name
From (SELECT level, SUBSTR (' Test reverse ', level, 1) NAME
From DUAL
CONNECT by Level <= LENGTH (' Test reverse ')
ORDER by 1 DESC);
SELECT Listagg (str) within
GROUP (
Order by Ord)
From (SELECT rownum ord, substr (' Test reverse ', level *-1, 1) str
From dual
CONNECT by Level <= length (' Test reverse '));
Of course, Undocument is best not to use, here 11g recommend the use of Listagg, or non-Chinese utl_raw. Reverse implementation.