使用 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
相关文章