Something I didn’t know: The REVERSE function

I was playing the pl/sql challenge the other day and the question was to identify valid implementations for a “reverse_string” function taking a varchar2 as the input and returning another varchar2 with the characters in inverted order.

One of the possible answers was this:

FUNCTION reverse_string (in_string VARCHAR2)
RETURN VARCHAR2
AS
  l_return   VARCHAR2 (100);
BEGIN
  SELECT REVERSE (in_string) INTO l_return FROM DUAL;

  RETURN l_return;
END reverse_string;

My immediate reaction was to skip this choice because I have never seen REVERSE as a string manipulation function in Oracle; but then I thought…what if this is a tricky question.

So I ran this small query expecting a syntax error but guess what?

SELECT reverse('hello world')
FROM dual;

It worked! The function displayed dlrow olleh.

So REVERSE turned out to be an undocumented function that has been out there for a while. I did find blog posts from 2006 that talk about it. I guess I have been living under a rock all this time or maybe I never had the need for such functionality.

By the way, I’m not suggesting that you should go and write production code around this function, because it’s unsupported and may not even work properly with multi-byte character sets.

About these ads

2 Comments on “Something I didn’t know: The REVERSE function”

  1. Noons says:

    Cool! Learned something new. Thanks!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.