Oracle reverse string之种种实现

最新推荐文章于 2024-08-28 22:53:00 发布

编程小强

最新推荐文章于 2024-08-28 22:53:00 发布

阅读量1.8k

点赞数

分类专栏： Oracle开发

Oracle开发专栏收录该内容

38 篇文章 0 订阅

订阅专栏

转载自：http://blog.chinaunix.net/uid-7655508-id-4011549.html

Oracle SQL里有undocument function实现reverse string的功能，那么能否用其他方式实现呢？这里介绍几种方法：
1.undocument REVERSE FUNCTION

SELECT REVERSE('测试reverse') FROM dual;
--注意中文可能乱码
SELECT REVERSE('测试reverse') FROM dual;
--2次reverse正常
SELECT reverse(REVERSE('测试reverse')) FROM dual;

2.使用utl_raw.REVERSE

SELECT utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw('测试reverse'))) FROM dual;
--中文同上

3.递归SQL实现reverse,好处，中文的可以直接reverse
--带分隔符，选不存在字符串的特殊符号

WITH t AS
(SELECT '测试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递归with
WITH t (str, s, c)
AS (
SELECT '测试reverse' str, CAST(NULL AS VARCHAR2(15)) 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.使用undocument wm_concat OR 11g r2 listagg,好处，中文的可以直接reverse

SELECT REPLACE(WM_CONCAT(NAME), ',', '') REV_NAME
FROM (SELECT LEVEL, SUBSTR('测试reverse', LEVEL, 1) NAME
FROM DUAL
CONNECT BY LEVEL <= LENGTH('测试reverse')
ORDER BY 1 DESC);

SELECT listagg(str) within
GROUP(
ORDER BY ord)
FROM (SELECT rownum ord, substr('测试reverse', LEVEL * -1, 1) str
FROM dual
CONNECT BY LEVEL <= length('测试reverse'));

当然，undocument的东西最好别用，这里11g推荐使用listagg,或者非中文的用UTL_RAW.REVERSE实现。