Re: Funny problem with LIKE operator

You must have in mind the hour part of a date field.

And you must know that if you use like operator you may have bad performance.

Try this. This query can use an index if defined on HIREDATE. (range scan)

select * from emp
where hiredate >= to_date('01/01/1981','dd/mm/yyyy') and hiredate <to_date('01/01/1982','dd/mm/yyyy')

"Maulik" <maulik67_at_hotmail.com> wrote in message news:e5c3b3e5.0209160736.56ee6b18_at_posting.google.com...
> 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:37:20 CDT