News, views, tips and tricks on Oracle and other fun stuff

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> exec :p := 1;

  PL/SQL procedure successfully completed.


  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.


  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.

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

  This is a palindrome

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

  PL/SQL procedure successfully completed.


  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  /


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  /


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  /


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

  2  AS
  3     FUNCTION REVERSE (p_string_in IN VARCHAR2)
  4        RETURN VARCHAR2;
  5  END;
  6  /

Package created.

  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;
 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  /


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:

Filed in Oracle on 30 Aug 06 | Tags: , ,

Reader's Comments

  1. |

    Not only can you SELECT reverse(date_col), but you can

    UPDATE table SET date_col = REVERSE(date_col) 

    and watch all the errors as people try to select the data. There’s a reason why somethings are best left undocumented.

    Seriously, I think the main use of this is behind the scenes for reverse key indexes.,F4950_P8_CRITERIA:6163160472530 I’d be surprised if it is ever ‘documented’ for any other use.

  2. |

    Or insert:

    SQL> create table t (x date);
    Table created.
    SQL> insert into t values (reverse(sysdate));
    1 row created.
    SQL> select * from t;
    SQL> select to_char(x,'mm-dd-yyyy') y from t;

    Since REVERSE preserves the datatype, you end up with a date that is not really a date. You are actually, in a way, bypassing datatype validation. Interesting!

    You’re right Gary, no wonder it is still undocumented!

  3. |

    I need reverse string. Like I have put anil kumar but i want the Kumar anil

  4. |

    I Need FAQ’s on Sql and Pl/Sql.If u have this info. plz farward to this mail.Thanking You.Bye

  5. |

    CREATE OR REPLACE FUNCTION Fn_Sentence (pi_input_string IN VARCHAR2 — Any combination of words in a string ,pi_skip_word_with IN VARCHAR2 DEFAULT NULL) — optional extention for special words RETURN VARCHAR2

    – Purpose : To return a string in conventional English language sentence format (letter case) (as opposed to INITCAP) – It will leave words that contain numbers, and a select range of characters, as they are – Usage : Small scale data conversion and display – Limitations : Does not use any dictionary – based purely on sentence conventions – Does not cope with Names and acronyms, but a special character (eg ^) – can be added to a word and subsequently removed to provide special formatting – Author : Rob Spaanderman 25/01/07 – Notes : The lions share of the convoluted code is to accommodate special words – words with non-alpha’s in them – It could be somewhat simplified if we were to ignore the IT potential for codes etc. – This function can be modified to suit your particular situation – I have tried to make it easily modifiable – by just changing the lists of special characters to suit

    IS c_translate_mask CONSTANT VARCHAR2(20) := ‘~~~~~~~~~~~~~~~~~~~’; — works in tandem with the word skip list and the translate check c_translate_check CONSTANT VARCHAR2(1) := ‘~’; — if this is changed, the translate mask must be changed c_upper_after_space CONSTANT VARCHAR2(20) := ‘.?!;:’; — English language symbols denoting the end of a sentence (; and : are debateable) c_upper_after_char CONSTANT VARCHAR2(20) := ‘”(‘; — Other characters that may preceed that start of a sentence or word with uppercase first letter c_skip_word_with CONSTANT VARCHAR2(20) := NVL(pi_skip_word_with,’0123456789_-&$@%/\’); — characters can be added or removed (or overridden) to change which words are ommitted from formatting

    v_length NUMBER(4) := LENGTH(pi_input_string); — there are multiple refences to the length v_first_blank_pos NUMBER(4) := 1; v_next_blank_pos NUMBER(4) := INSTR(pi_input_string,’ ‘); v_previous_letter VARCHAR2(1) := SUBSTR(pi_input_string,1,1); v_current_letter VARCHAR2(1) := v_previous_letter; — when starting, we start at position 1, so everything starts with letter 1 v_next_letter VARCHAR2(1) := v_previous_letter; v_result_string VARCHAR2(2000); v_current_word VARCHAR2(2000); — used to determine whether we need to leave a special word alone or not

    BEGIN FOR v_position IN 1..v_length LOOP IF v_current_letter = ‘ ‘ — between words OR v_position = 1 THEN — first time around v_first_blank_pos := v_position; v_next_blank_pos := INSTR(pi_input_string,’ ‘,v_first_blank_pos + 1); IF v_next_blank_pos = 0 THEN — probably the end of the sentence which will have no space v_next_blank_pos := v_length; END IF; — Extract the next word in the sentence – used to determine if we need to leave it alone or not v_current_word := SUBSTR(pi_input_string, v_first_blank_pos, v_next_blank_pos – v_first_blank_pos); END IF; — We check to see if there any special characters in the word – if not, do the formatting, else skip the formatting IF INSTR(TRANSLATE(v_current_word, c_skip_word_with, c_translate_mask), c_translate_check) = 0 THEN IF (v_current_letter = ‘ ‘ AND INSTR(c_upper_after_space,v_previous_letter) <> 0 OR v_position = 1) OR INSTR(c_upper_after_char, v_current_letter) <> 0 THEN v_next_letter := UPPER(v_next_letter); ELSE v_next_letter := LOWER(v_next_letter); END IF; END IF; — The “next letter” contains either the uppered, the lowered, or the left alone value v_result_string := v_result_string || v_next_letter; — Move the three operational letters along the sequence v_previous_letter := v_current_letter; v_current_letter := v_next_letter; v_next_letter := SUBSTR(pi_input_string,v_position + 1,1); END LOOP; RETURN (v_result_string); END; /

  6. |

    I just tried in 10g XE

    INSERTing REVERSE(Date) has interesting results. UPDATing, however, seems to choose a valid DATE.