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.