Funny problem with LIKE operator

Hello all,

I m running Oracle 8.1.6, and am having this weird behaviour which i cannot explain. Would appreciate it, if someone would be kind enough to offer explanation for this

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                
SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -----------------
---------- ---------- ----------
      7369 SMITH      CLERK           7902 17121980 00:00:00       
800                    20
      7499 ALLEN      SALESMAN        7698 20021981 00:00:00      
1600        300         30
      7521 WARD       SALESMAN        7698 22021981 00:00:00      
1250        500         30
      7566 JONES      MANAGER         7839 02041981 00:00:00      
2975                    20
      7654 MARTIN     SALESMAN        7698 28091981 00:00:00      
1250       1400         30
      7698 BLAKE      MANAGER         7839 01051981 00:00:00      
2850                    30
      7782 CLARK      MANAGER         7839 09061981 00:00:00      
2450                    10
      7788 SCOTT      ANALYST         7566 09121982 00:00:00      
3000                    20
      7839 KING       PRESIDENT            17111981 00:00:00      
5000                    10
      7844 TURNER     SALESMAN        7698 08091981 00:00:00      
1500          0         30
      7876 ADAMS      CLERK           7788 12011983 00:00:00      
1100                    20
      7900 JAMES      CLERK           7698 03121981 00:00:00       
950                    30
      7902 FORD       ANALYST         7566 03121981 00:00:00      
3000                    20
      7934 MILLER     CLERK           7782 23011982 00:00:00      
1300                    10

14 rows selected.

SQL> select ename,hiredate from emp
  2 where hiredate like '%81';

no rows selected

SQL> select ename,hiredate from emp
  2 where hiredate like '%81%';

ENAME HIREDATE

---------- -----------------
ALLEN      20021981 00:00:00
WARD       22021981 00:00:00
JONES      02041981 00:00:00
MARTIN     28091981 00:00:00
BLAKE      01051981 00:00:00
CLARK      09061981 00:00:00
KING       17111981 00:00:00
TURNER     08091981 00:00:00
JAMES      03121981 00:00:00
FORD       03121981 00:00:00

10 rows selected.

When using LIKE '%81' no rows are returned!!! This behaviour is pretty strange to me, because, as per Oracle Reference Manuals, "%" operator is defined to be "representing any sequence of zero or more characters"..

Any explanations to this behaviour will be very much welcomed...

Thanks in advance Received on Mon Sep 16 2002 - 10:36:42 CDT