Re: LIKE operator on CHAR column when there is no wildcard character
Date: Mon, 7 Jul 2008 06:55:59 -0700 (PDT)
On Jul 7, 2:29 pm, Veeru71 <m_ad..._at_hotmail.com> wrote:
> Hi Dion,> 1. Do you use literal values?
> > 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
> 184.108.40.206 & 10.2.0.1 ?
> create table test(col char(10));
> insert into test values ('ABC')
> select * from test where col LIKE 'ABC'
> In 220.127.116.11, 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.
Check out bug 4397534 seems to be your issue. Pertinent bit is
"The same behavior in Oracle Server 8.0.6 - 18.104.22.168,
but fixed in 22.214.171.124/10.1.0.1 (bug 1257252 / bug 2695073 / bug 2883715)"
Thanks. Received on Mon Jul 07 2008 - 08:55:59 CDT