Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Reverse String Word by Word using SQL

Tags:

sql

oracle

plsql

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

like image 957
ahairshi Avatar asked Jan 07 '14 14:01

ahairshi


People also ask

How do you reverse a word in a string in SQL?

SQL Server REVERSE() Function The REVERSE() function reverses a string and returns the result.

How do I match a word in SQL?

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.


2 Answers

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.

like image 170
Alex Poole Avatar answered Sep 22 '22 03:09

Alex Poole


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
like image 45
Maheswaran Ravisankar Avatar answered Sep 22 '22 03:09

Maheswaran Ravisankar