Re: Funny problem with LIKE operator

Just an addition; all DATE fields in Oracle are DateTime so will always have a time component that must be dealt with..As you saw from your data, the Time is set to Midnight (00:00:00)if no explicit time is given on input.

This has a great effect when comparing dates; If the TIME is not relevant, use 'Where Trunc(DateField1) < Trunc(DateField2) ' or similar to avoid comparing the time part..

Michael Gast <michael.gast_at_seppmed.de> wrote:

>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

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------

   http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =----- Received on Mon Sep 16 2002 - 11:10:28 CDT