使用SQL逐字反向字符串 [英] Reverse String Word by Word using SQL

问题描述

我需要颠倒句子或字符串中的单词位置.

I would need to reverse the word positions in a sentence or String.

For example : "Hello World! I Love StackOverflow", to be displayed as "StackOverflow Love I World! Hello". 

可以用SQL完成吗?字长不大于VARCHAR2(4000)，这是Oracle VARCHAR2表列中支持的最大长度.

Can it be done with a SQL ? The word length is no greater than VARCHAR2(4000) which is the maximum length support in a Oracle VARCHAR2 table column.

我得到了仅反转字符串(字符以相反顺序)的解决方案

I got solutions for reversing a string (Characters in reverse order) only

推荐答案

创建函数:

REGEXP_SUBSTR('Your text here','[^ ]+', 1, ?)将使用空格作为定界符从文本中提取一个单词. Tt在Exception上返回原始String本身！

REGEXP_SUBSTR('Your text here','[^ ]+', 1, ?) will extract a word from the text using Space as a delimiter. Tt returns the original String itself on Exception!

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;
/

样本结果:

Sample Result:

您可以致电reverse_words(yourcolumn) from your_table

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

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

这篇关于使用SQL逐字反向字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！