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

From: Doug T <>
Date: Mon, 7 Jul 2008 06:55:59 -0700 (PDT)
Message-ID: <>

On Jul 7, 2:29 pm, Veeru71 <> 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
> & ?
> create table test(col char(10));
> insert into test values ('ABC')
> select * from test where col LIKE 'ABC'
> In, it is returning 1 row.
> In, 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 -,

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

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

Original text of this message