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

Hi Dion,
> 1. Do you use literal values?

   Yes
> 2. How many distinct values for like 'xxx' condition?

   Actually the pattern (2nd operand of LIKE) is submitted through GUI by the app users and the SQL will be dynamically built by the app. It can also have a '%' as part of the input string. Eg. Users can perform a search on CITY column by entering strings like 'BOS%' or they can enter the full string 'BOSTON' without any '%'. So the SQL that is generated on the fly would be SELECT .....WHERE CITY LIKE 'BOS%' or SELECT...WHERE CITY LIKE 'BOSTON' depending on the user input.

Hi Sybrand,
Thanks for you detailed reply and you are right, we should have used varchar2 cols instead of char cols in the first place but this is a legacy app. I know CHAR cols will be blank-padded, etc....but my basic question is - why the following SQL is giving different results in 9.2.0.4 & 10.2.0.1 ?

create table test(col char(10));
insert into test values ('ABC')
select * from test where col LIKE 'ABC'

In 9.2.0.4, it is returning 1 row.
In 10.2.0.1, it is returning 0 rows.

I am trying to figure out whether (1) this is an intended functional change in 10g OR (2) it was a bug in 9i that got fixed in a later version OR (3) it is a bug in 10g.

Thanks Received on Mon Jul 07 2008 - 08:29:12 CDT