H.Tonguç YILMAZ Blog

CREATE TABLE palidrome_test ( text VARCHAR2(100) );

INSERT ALL
INTO palidrome_test VALUES (‘Eat More Bananas’)
INTO palidrome_test VALUES (‘A Toyota’’s a Toyota’)
INTO palidrome_test VALUES (‘Never odd or even.’)
INTO palidrome_test VALUES (‘Some men interpret nine memos.’)
INTO palidrome_test VALUES (‘Palindromes are the enemy of productivity’)
SELECT * FROM dual;

SELECT text
, CASE
WHEN text_filtered = REVERSE(text_filtered)
THEN ‘Yes’
ELSE ‘No’
END AS palindrome
FROM   ( SELECT text, UPPER(REPLACE(TRANSLATE(text,’ ”,.:;!?”‘,’ ‘),’ ‘)) AS text_filtered
FROM   palidrome_test );

TEXT                                           PAL
———————————————- —
Eat More Bananas                               No
A Toyota’s a Toyota                            Yes
Never odd or even.                             Yes
Some men interpret nine memos.                 Yes
Palindromes are the enemy of productivity      No

5 rows selected.

ps: Strange that REVERSE is not documented – seems like a documentation bug as it’s been around since at least 8i.  Converting a VARCHAR2 to RAW data type to use the REVERSE function in the UTL_RAW package, all that could be replaced by the (unsupported) REVERSE function as shown in my example;
SQL> ed
Wrote file afiedt.buf

1  declare
2  type type_name is varray(20) of varchar2(20);
3  p1 type_name;
4  i PLS_INTEGER;
5  j number := null;
6  k number := null;
7  begin
8  p1 := type_name(‘liril’,'toto’);
9  for j in p1.first..p1.last loop
10     if p1(j)=UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.REVERSE(UTL_RAW.CAST_TO_RAW(p1(j)))) then
11        dbms_output.put_line(p1(j)||’ is a palindrome’);
12     else
13        dbms_output.put_line(p1(j)||’ is not a palindrome’);
14     end if;
15  end loop;
16* end;
SQL> /
liril is a palindrome
toto is not a palindrome

PL/SQL procedure successfully completed.

This entry was posted on Friday, August 25th, 2006 at 6:08 pm and is filed under Oracle. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.