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

On Jul 7, 2:29 pm, Veeru71 <m_ad..._at_hotmail.com> wrote:
> 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

Check out bug 4397534 seems to be your issue. Pertinent bit is

"The same behavior in Oracle Server 8.0.6 - 9.2.0.4,

but fixed in 9.2.0.5/10.1.0.1 (bug 1257252 / bug 2695073 / bug 2883715)"

Thanks. Received on Mon Jul 07 2008 - 08:55:59 CDT