Re: Funny problem with LIKE operator

Hi Maulik,

> 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%';
>
> 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...

Have a look on your output: In the first row you have the string '20021981 00:00:00' as output. LIKE '%somewhat' means that a character string ends with the substring 'somewhat'. In your example there are other characters at the end of the string. Therefore it is not astonishing that '%81%' gives you what you expect to get with '%81'.

Why am i talking of character strings here? LIKE works with character strings. Therefore Oracle performs an implizit data type conversion of your hiredate column (which is defined as datetime, i assume) to a string to execute the LIKE operation. That's why the time part of the hiredate takes into account.

Michael Gast Received on Mon Sep 16 2002 - 10:54:38 CDT