Oracle Reverse String implementations ____oracle

Source: Internet
Author: User
Tags ord

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.