Home » PLSQL » Reversing a String in SQL and PL/SQL An Example

Reversing a String in SQL and PL/SQL An Example

By
You can reverse a string in SQL by using Reverse function of SQL. It is very simple to use, following is the command to reverse a string:
SELECT reverse ('fox infotech') FROM DUAL;
And if you want to reverse a string manually using PL/SQL block, here is the example:
set serveroutput on;
DECLARE
   v_string    VARCHAR2 (1000);
   v_reverse   VARCHAR2 (1000);
BEGIN
   v_string := 'fox infotech';
   FOR n IN REVERSE 1 .. LENGTH (v_string)
   LOOP
      v_reverse := v_reverse || SUBSTR (v_string, n, 1);
   END LOOP;
   DBMS_OUTPUT.put_line (v_reverse);
END;
/
You can create your own function of above PLSQL block, as follows:

CREATE OR REPLACE FUNCTION my_reverse (v_string IN VARCHAR2)
   RETURN VARCHAR2
IS
   v_reverse   VARCHAR2 (4000);
BEGIN
   FOR n IN REVERSE 1 .. LENGTH (v_string)
   LOOP
      v_reverse := v_reverse || SUBSTR (v_string, n, 1);
   END LOOP;
   RETURN (v_reverse);
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN ('');
END;
/

Use this function in your query:

SELECT my_reverse ('fox infotech') FROM DUAL;