使用SQL反向字符串Word

基于XML的版本,以避免定义自己的功能; 需要11克listagg():

select listagg(word, ' ') within group (order by rn desc) as reversed
from (
  select word, rownum as rn
  from xmltable('for $i in ora:tokenize($STR, " ") return $i'
    passing 'Hello World! I Love StackOverflow' as str
    columns word varchar2(4000) path '.'
  )
);

REVERSED                               
----------------------------------------
StackOverflow Love I World! Hello        

该XMLTable()做的tokenising,并分配行号:

select rownum as rn, word
from xmltable('for $i in ora:tokenize($STR, " ") return $i'
  passing 'Hello World! I Love StackOverflow' as str
  columns word varchar2(4000) path '.'
);

        RN WORD               
---------- --------------------
         1 Hello                
         2 World!               
         3 I                    
         4 Love                 
         5 StackOverflow        

在listagg()随后片它以相反的顺序重新走到一起.

创建一个函数:

REGEXP_SUBSTR('Your text here','[^ ]+', 1, ?)将使用Space作为分隔符从文本中提取单词.Tt在Exception上返回原始String本身!

CREATE OR REPLACE FUNCTION reverse_words (v_STRING IN VARCHAR2)
RETURN VARCHAR2
IS
   L_TEMP_TEXT  VARCHAR2(4000);
   L_FINAL_TEXT  VARCHAR2(4000);
   V_LOOPCOUNT NUMBER :=0;
   T_WORD VARCHAR2(4000);
BEGIN
      L_TEMP_TEXT := regexp_replace(V_STRING,'[[:space:]]+',' '); -- Replace multiple spaces as single
      LOOP
        v_LOOPCOUNT := v_LOOPCOUNT+1;
        T_WORD      := REGEXP_SUBSTR(L_TEMP_TEXT,'[^ ]+', 1, V_LOOPCOUNT);
        L_final_TEXT := T_WORD||' '||L_final_TEXT;
      EXIT WHEN T_WORD IS NULL;
      END LOOP;
   RETURN(TRIM(L_final_TEXT));
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(sqlerrm||chr(10)||dbms_utility.format_error_backtrace);
        RETURN V_STRING;
END reverse_words;
/

样本结果:

你可以打电话 reverse_words(yourcolumn) from your_table

SQL> select reverse_words('Hello World! I Love StackOverflow') "Reversed" from dual;

Reversed
--------------------------------------------------------------------------------
StackOverflow Love I World! Hello