Cool Undocumented SQL Function: REVERSE

Let’s say that you want to check whether a word or a sentence reads the same backward or forward, how do you do it in SQL? you use the REVERSE function. We’ll see an example of that below, but first, a warning. Unlike LNNVL, REVERSE is still an undocumented SQL function, which means that it should be used with caution because “undocumented” also means “not supported”. With that warning out of the way, let’s explore this undocumented function and find out what other documented alternatives exist that have the same functionality as REVERSE.

REVERSE has been around since 8i, maybe even prior to 8i. I have tested it on 8.1.7 and it worked. Since REVERSE is undocumented, the best way to know what it does is to try it out. So, let’s go through a few examples: 

  EDDIE@XE> var p VARCHAR2(10);
  EDDIE@XE> exec :p := 'oracle';

  PL/SQL procedure successfully completed.

  EDDIE@XE> SELECT 'straight' r,
    2         :p p,
    3         DUMP (:p) dumpp
    4    FROM DUAL
    5  UNION ALL
    6  SELECT 'Reverse',
    7         REVERSE (:p),
    8         DUMP (REVERSE (:p))
    9    FROM DUAL;

  R        P        DUMPP
  -------- -------- ----------------------------------------
  straight oracle   Typ=1 Len=6: 111,114,97,99,108,101
  Reverse  elcaro   Typ=1 Len=6: 101,108,99,97,114,111

Ok, as expected, if you give REVERSE a string, it returns that string in reverse. Notice that DUMP returns Typ=1, which means that the datatype for both the input and the reversed output is VARCHAR2.

  EDDIE@XE> var p NUMBER;
  EDDIE@XE> exec :p := 1;

  PL/SQL procedure successfully completed.

  EDDIE@XE> /

  R                 P DUMPP
  -------- ---------- ----------------------------------------
  straight          1 Typ=2 Len=2: 193,2
  Reverse  -k.00E+120 Typ=2 Len=2: 2,193

Now, if we feed REVERSE a number, it executes successfully. Notice that DUMP returns Typ=2, which means that the datatype for both the input and the reversed output is NUMBER. Even though the reverse of 1 is “-k.00E+120″ which is not exactly meanigful, the internal representation of the number 1 was indeed reversed from “193,2″ to “2,193″. Here is another example of a NUMBER input:

  EDDIE@XE> exec :p := 1234;

  PL/SQL procedure successfully completed.

  EDDIE@XE> /

  R                 P DUMPP
  -------- ---------- ----------------------------------------
  straight       1234 Typ=2 Len=3: 194,13,35
  Reverse  -8.800E+55 Typ=2 Len=3: 35,13,194

What about a DATE input?

  EDDIE@XE> SELECT 'straight' r,
    2         sysdate p,
    3         DUMP (sysdate) dumpp
    4    FROM DUAL
    5  UNION ALL
    6  SELECT 'Reverse',
    7         REVERSE (sysdate),
    8         DUMP (REVERSE (sysdate))
    9    FROM DUAL;

  R        P         DUMPP
  -------- --------- ----------------------------------------
  straight 25-AUG-06 Typ=13 Len=8: 214,7,8,25,15,14,2,0
  Reverse  15-NO -12 Typ=13 Len=8: 0,2,14,15,25,8,7,214

The REVERSE function did to the date the same as what it did to the number, no meaningful output, but the internal representation of the value was reversed.

So, what have we learned so far? The undocumented SQL function REVERSE does what its name implies. It is only useful when you apply it to string data types. As an example of its usefulness:

  EDDIE@XE> var p VARCHAR2(12);
  EDDIE@XE> exec :p := 'detartrated';

  PL/SQL procedure successfully completed.

  EDDIE@XE> SELECT CASE
    2            WHEN REVERSE (:p) = :p
    3               THEN 'This is a palindrome'
    4            ELSE 'No luck'
    5         END o
    6    FROM DUAL;

  O
  --------------------
  This is a palindrome

  EDDIE@XE> exec :p := 'mom '

  PL/SQL procedure successfully completed.

  EDDIE@XE> /

  O
  --------------------
  No luck

In case you do not know what a palindrome is, it is a word, phrase, verse, or sentence that reads the same backward or forward.

Now after you’ve been exposed to this simple function REVERSE, you’re trying hard to avoid using it in production code because it is undocumented. Is there another alternative? you ask. Well, there is the UTL_RAW.REVERSE function which is totally documented and safe to use in production code. Let’s give it a quick try:

  EDDIE@XE> SELECT UTL_RAW.cast_to_varchar2
    2           (UTL_RAW.REVERSE (UTL_RAW.cast_to_raw ('oracle'))) o
    3    FROM DUAL
    4  /

  O
  ----------
  elcaro

It even works on numbers:

  EDDIE@XE> SELECT UTL_RAW.cast_to_varchar2
    2           (UTL_RAW.REVERSE (UTL_RAW.cast_to_raw (1234))) o
    3    FROM DUAL
    4  /

  O
  ----------
  4321

And dates (in a funny way!):

  EDDIE@XE> SELECT UTL_RAW.cast_to_varchar2
    2           (UTL_RAW.REVERSE (UTL_RAW.cast_to_raw (sysdate))) o
    3    FROM DUAL
    4  /

  O
  ----------
  60-GUA-52

If you do not like using UTL_RAW.REVERSE, you can write your own REVERSE PL/SQL function like this:

EDDIE@XE> CREATE OR REPLACE PACKAGE my_string_util_pkg
  2  AS
  3     FUNCTION REVERSE (p_string_in IN VARCHAR2)
  4        RETURN VARCHAR2;
  5  END;
  6  /

Package created.

EDDIE@XE> CREATE OR REPLACE PACKAGE BODY my_string_util_pkg
  2  AS
  3     FUNCTION REVERSE (p_string_in IN VARCHAR2)
  4        RETURN VARCHAR2
  5     IS
  6        l_reverse_string   VARCHAR2 (32767);
  7     BEGIN
  8        IF (p_string_in IS NULL)
  9        THEN
 10           l_reverse_string := NULL;
 11        ELSE
 12           FOR i IN REVERSE 1 .. LENGTH (p_string_in)
 13           LOOP
 14              l_reverse_string :=
 15                    l_reverse_string
 16                 || SUBSTR (p_string_in, i, 1);
 17           END LOOP;
 18        END IF;
 19
 20        RETURN l_reverse_string;
 21     END REVERSE;
 22  END;
 23  /

Package body created.

EDDIE@XE> column reversed format A10
EDDIE@XE> SELECT my_string_util_pkg.reverse ('Oracle') reversed
  2    FROM DUAL
  3  /

REVERSED
----------
elcarO

So, from now until REVERSE becomes documented, use UTL_RAW.REVERSE instead, even if it requires this extra RAW conversion, or just write your own function.

Sources and resources:

Possibly related:

Tagged , , | Post a Comment