Re: LIKE operator on CHAR column when there is no wildcard character

On Sat, 5 Jul 2008 23:59:04 -0700 (PDT), Veeru71 <m_adavi_at_hotmail.com> wrote:

>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).

This is incorrect. XYZ is stored with trailing spaces. All text stored in CHAR columns is always stored padded to the maximum lenght. The comparison is using fixed length semantics, so the right hand expression is automatically padded to 30 characters. This is why it works.
Oracle automatically switches between variable length semantics and fixed length semantics.

>
>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.

This is again incorrect. Fixed length semantics being used. LIKE without wildcard char is always identical to using the equality operator.

But in 10g, the trailing spaces in
>the column value are making a difference.
>

If that is true, which I doubt, it is either described in the New Features Manual, or a bug which has been removed in a patch release.

>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.

This is the #1 recipe for unscalable applications.
>
>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 ?
>

It is pretty daft to use CHAR columns where a VARCHAR2 would have been way more appropiate. If you don't want to find whether this is a bug in 10g which has been fixed in the meantime your only choice is to set the compatible parameter to somthing like 9.2.0.0, *provided* it wasn't set to 10..... when you created the database. Obviously you will now have a 9i database in a 10g guise, and no 10g new features will be working.

>Thanks
>Murty

-- 
Sybrand Bakker
Senior Oracle DBA