使用 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 在异常时返回原始字符串本身！

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

示例结果:

您可以从 your_table 中调用 reverse_words(yourcolumn)

You can call 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屋！