Re: Funny problem with LIKE operator

On 16 Sep 2002 08:36:42 -0700, maulik67_at_hotmail.com (Maulik) wrote:

>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

Hi Maulik,

At first "like '%81'" means " matches strings that END with '81' ". None of your hiredates do...so the above behaviour is quite meaningful.

Secondly, you are on your way to hell ( Sybrand's expression). You are using the the 'like' condition on a DATE field, though it only applies to character expressions. Which means that Oracle IMPLICITLY converts your date field to a char expression.

And how does is do that ?

It converts the DATE field to the *default date format* for your session, and that can vary from session to session. So you may actually get different results from your query, depending on the session that executes it!

To get out of that mess, always use EXPLICIT conversion, like this :

select ename,hiredate from emp

     where to_char(hiredate,YYYY-MM-DD') like '%81%';

• Kenneth Koenraadt