LIKE operator on CHAR column when there is no wildcard character

Giganews Newsgroups
Subject: LIKE operator on CHAR column when there is no wildcard character
Posted by:  Veeru71 (m_ada…@hotmail.com)
Date: Sat, 5 Jul 2008

We are trying to upgrade our database  from Oracle 9i to 10g.

We have a CHAR(30) column in a table.

When we perform string comparisons using '='  operator (Eg.....where
col = 'XYZ' )  the trailing spaces are ignored, which is perfectly
fine (both in 9i & 10g).

When we use LIKE operator without specifying any wildcard char in
Oracle 9i (Eg.....where col  like 'XYZ'), the trailing spaces are
being ignored  and so the behavior of  "LIKE without wildcard char" is
identical to direct '=' operator.  But in 10g, the trailing spaces in
the column value are making a difference.

I know that it doesn't make much sense to use LIKE operator when there
is no wildcard char but the SQL is dynamically generated based on some
complex conditions in the app.

So my question is what is the expected behaviour of LIKE operator on
CHAR column  with regard to trailing spaces when there is no wildcar
character?

Is it possible to simulate 9i behavior in 10g  by setting any config
param ?

Thanks
Murty

Replies