Re: Funny problem while using the LIKE operator

Maulik,

Try this:

alter session set nls_date_format 'ddmmyyyy';

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

regards,
Peter

"Maulik" <maulik67_at_hotmail.com> schreef in bericht news:e5c3b3e5.0209170545.55ac8cca_at_posting.google.com...
> Hello all,
>
> Please pardon me if this is my second posting on the same subject.
>
> Oracle 8.1.6,
> sqlplus
>
> I m using the database example table(EMP and DEPT) which come standard
> with the oracle database installation.
>
> Oracle8i Enterprise Edition Release 8.1.6.3.0 - Production
> With the Partitioning option
> JServer Release 8.1.6.3.0 - Production
>
> 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 from emp where hiredate like '%81';
>
> no rows selected
>
> SQL> select ename from emp where hiredate like '%81%';
>
> ENAME
> ----------
> ALLEN
> WARD
> JONES
> MARTIN
> BLAKE
> CLARK
> KING
> TURNER
> JAMES
> FORD
>
> 10 rows selected.
>
> Upon selecting LIKE operator with '%81', i do not get any rows!!! Is
> this behaviour normal??? because in other words i am asking it return
> to me the data with hiredate ending in 81! And the funny thing about
> this is that, this example is given in Oracle Reference library!!!
>
> Your help is very much appreciated
Received on Tue Sep 17 2002 - 09:00:46 CDT