Re: PROBLEM WITH LIKE OPERATOR IN SELECT WHERE CLAUSE

I found out that this is the way it is in oracle 8.0.5 - see below... LIKE Operator
The optimizer simplifies conditions that use the LIKE comparison operator to compare an expression with no wildcard characters into an equivalent condition that uses an equality operator instead. For example, the optimizer simplifies the first condition below into the second:

ename LIKE 'SMITH'  

ename = 'SMITH'
The optimizer can simplify these expressions only when the comparison involves variable-length datatypes. For example, if ENAME was of type CHAR(10), the optimizer cannot transform the LIKE operation into an equality operation due to the equality operator following blank-padded semantics and LIKE not following blank-padded semantics.

  "shamai markel" <shamaim_at_amdocs.com> wrote in message news:990687848.408648_at_sointnews.amdocs.com...   Hi,

1. In difference from = operator, LIKE opearator does not ignore trailing blanks in CHAR type fields.
2. This means that for a CHAR(5) field containing ABC , the following SELECT statement will return no lines : SELECT * FROM table WHERE field LIKE A%C , whereas following SELECT statement will return the line : SELECT * FROM table WHERE field = ABC .
3. Does anyone have a bypass to this problem that doesn t require to pad blanks to the searched string and that doesn t inluence performance ? thanks very much shamai

--