如何在不使用 REVERSE() 函数的情况下反转 Oracle (11g) SQL 中的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35314500/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to reverse a string in Oracle (11g) SQL without using REVERSE() function
提问by user5355231
I am trying to reverse a string without using REVERSE function. I came across one example which is something like:
我试图在不使用 REVERSE 函数的情况下反转字符串。我遇到了一个例子，它是这样的：
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?
除了这种方法，有没有其他更好的方法来做到这一点？
采纳答案by Alex Poole
If you're trying to avoid the undocumented reverse()function you could use the utl_raw.reverse()functioninstead, with appropriate conversion too and from RAW:
如果您试图避免未记录的reverse()功能，您可以使用该utl_raw.reverse()功能，并进行适当的转换并从 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().
所以这是取一个原始值；这样做utl_i18n.string_to_raw()；然后将其传递给utl_raw.reverse(); 然后将结果传回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:
或者从讨论@RahulTripathi 链接到的变体，没有字符集处理：
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.
但该线程还指出它仅适用于单字节字符。
回答by Boneist
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!
注意我使用了一个分隔符，~|只是因为它不太可能成为你的字符串的一部分。您需要为 sys_connect_by_path 提供一个非空分隔符，因此我没有将其留空！
回答by Vaas
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')
);
回答by Rahul Tripathi
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> /
