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".
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
SQL Server REVERSE() Function The REVERSE() function reverses a string and returns the result.
SQL pattern matching allows you to search for patterns in data if you don't know the exact word or phrase you are seeking. This kind of SQL query uses wildcard characters to match a pattern, rather than specifying it exactly. For example, you can use the wildcard "C%" to match any string beginning with a capital C.
XML-based version to avoid defining your own function; requires 11g for 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
The XMLTable()
does the tokenising, and assigns a row number:
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
The listagg()
then pieces it back together in reverse order.
Create a Function:
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:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With