A Procedure to Reverse a String in Pl/Sql

A procedure to Reverse a String in PL/SQL

Two things - you shouldn't specify the datatype size in procedure's/function's parameter list and you do not need the DECLARE keyword. Try this:

CREATE OR REPLACE PROCEDURE ReverseOf(input IN varchar2) IS
        rev varchar2(50):='';
BEGIN
        FOR i in reverse 1..length(input) LOOP
                rev := rev||substr(input, i, 1);
        END LOOP;
        dbms_output.put_line(rev);
END;

While loop PL/SQL read a string backwards

Here's one option:

SQL> set serveroutput on
SQL>
SQL> declare
  2    l_str   varchar2(20) := 'Littlefoot';
  3    i       number;
  4    retval  varchar2(20);
  5  begin
  6    i := length(l_str);
  7    while i <> 0 loop
  8      retval := retval || substr(l_str, i, 1);
  9      i := i - 1;
 10    end loop;
 11    dbms_output.put_line(retval);
 12  end;
 13  /
toofelttiL

PL/SQL procedure successfully completed.

SQL>

---

If it doesn't have to be PL/SQL, hierarchical query along with listagg might be another way to do it:

SQL> var l_str varchar2(20)
SQL> exec :l_str := 'Littlefoot';

PL/SQL procedure successfully completed.

SQL> select listagg(substr(:l_str, length(:l_str) - level + 1, 1), '') within group (order by level) result
  2  from dual
  3  connect by level <= length(:l_str);

RESULT
--------------------------------------------------------------------------------
toofelttiL

SQL>

How to reverse a string in Oracle (11g) SQL without using REVERSE() function

If you're trying to avoid the undocumented reverse() function you could use the utl_raw.reverse() function instead, with appropriate conversion too and from RAW:

select utl_i18n.raw_to_char(
  utl_raw.reverse(
    utl_i18n.string_to_raw('Some string', 'AL32UTF8')), 'AL32UTF8')
from dual;

UTL_I18N.RAW_TO_CHAR(UTL_RAW.REVERSE(UTL_I18N.STRING_TO_RAW('SOMESTRING','AL32UT
--------------------------------------------------------------------------------
gnirts emoS                                                                     

So that is taking an original value; doing utl_i18n.string_to_raw() on that; then passing that to utl_raw.reverse(); then passing the result of that back through utl_i18n.raw_to_char().

Not entirely sure how that will cope with multibyte characters, or what you'd want to happen to those anyway...

Or a variation from the discussion @RahulTripathi linked to, without the character set handling:

select utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw('Some string')))
from dual;

UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW('SOMESTRING')))   
--------------------------------------------------------------------------------
gnirts emoS                                                                     

But that thread also notes it only works for single-byte characters.

How to reverse a string after tokenizing it in SQL

If possible, the best solution would be to change your data so that each value is stored in a different row.

If that doesn't work, you can create a PL/SQL function.

If you want a purely SQL solution, typically you'll have to split each value into multiple rows (cross join with an object table, or connect by level <= max number of items), and then re-aggregate the data using one of a dozen different methods (listagg, collect, stragg, xml, sys_connect_by_path, etc.)

Another SQL-only way is to use regular expressions. This is probably the fastest, but it only works with up to 9 items because Oracle only supports 9 back references:

--Get everything except the extra ':' at the end.
select substr(string, 1, length(string) - 1) string from
(
  select regexp_replace(
    --Add a delimter to the end so all items are the same
    'L3:L2:L1:L0'||':'
    --Non-greedy search for anything up to a : (I bet there's a better way to do this)
    ,'(.*?:)?(.*?:)?(.*?:)?(.*?:)?(.*?:)?(.*?:)?(.*?:)?(.*?:)?(.*?:)?(.*?:)?'
    --Reverse the back-references
    ,'\9\8\7\6\5\4\3\2\1') string
  from dual
);

Reverse String Word by Word using SQL

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

Reversing a string using an index in Oracle

There is the myth that a reverse key index can be used for that, however, I've never seen that in action.

I would try a "manual" function based index.

CREATE INDEX REVERSE_STR_IDX on TBL(reverse(string));

SELECT *
  FROM TBL
 WHERE reverse(string) LIKE '4321%';

Reverse the given number program in PL/SQL

A second option is using the REVERSE() function.

However, that function would like to input a CHAR, not a NUMBER.

So you will have to convert that first.

For example:

DECLARE
   N_Num NUMBER := 234;
   N_Rev NUMBER := 0;
BEGIN
   SELECT REVERSE(TO_CHAR(N_Num)) INTO N_Rev FROM Dual;
   DBMS_OUTPUT.PUT_LINE (N_Rev);
END;
/

Reversing numbers in Oracle SQL

Oracle's reverse function accepts a char, not a number, so you'd have to convert it:

SELECT   s_last||', '|| s_first||LPAD(ROUND(MONTHS_BETWEEN(SYSDATE, s_dob)/12,0),22,'*' ) AS "Student Name and Age", 
         s_pin AS "Pin", 
         REVERSE(TO_CHAR(s_pin)) AS "Coded Pin"
FROM     student
ORDER BY s_last;

NOTE REVERSE is an undocumented function. If you are using it in your application, you might have a risk in future, "IF" this feature is removed in a later version that you wish to upgrade to. And it's reasonably likely that they might end up being documented functions in future, who knows. So, use it at your own risk.

---

Related Topics