Re: Varchar2, is this strange or not?

This is not strange. You are implicitly using variable length semantics. This means two strings of inequal length are not padded in comparisons. Your idea is your where clause is test > 'x '. The varchar data type was meant to function as variable length string with fixed length semantics. AFAIK Oracle never implemented it.

Hth,

--
Sybrand Bakker, Oracle DBA
Huiming Lee <huiming_at_pop21.odn.ne.jp> wrote in message news:3860CBA8.A0447D4A_at_pop21.odn.ne.jp...
> The following is the test I did:
>
> create table test (id number(1), text varchar2(10));
> insert into test values (1, 'x');
> insert into test values (2, 'x '); --x with a space
> commit;
>
> SQL> select * from test;
>
> ID TEXT
> --------- ----------
> 1 x
> 2 x
>
> NO problem with this.
>
> SQL> select * from test where text = 'x';
>
> ID TEXT
> --------- ----------
> 1 x
>
> SQL> select * from test where text = 'x '; -- x with a space
>
> ID TEXT
> --------- ----------
> 2 x
>
> SO FAR SO GOOD.
>
> SQL> select * from test where text > 'x';
>
> no rows selected
>
> SQL> select * from test where text < 'x';
>
> no rows selected
>
> SQL>
>
> DO HAVE PROBLEM WITH THIS
>
> According to my understanding, for varchar2 type, 'x ' > 'x'
>
> I am using Oracle Lite.
>
> Anybody can give me an explain? Thanks in advance.
>
>
> This is directly from Oracle Document.
>
> Nonpadded Comparison Semantics
>
> Oracle compares two values character by character up to the first
> character that differs. The value with the
> greater character in that position is considered greater. If two values
> of different length are identical up to the
> end of the shorter one, the longer value is considered greater. If two
> values of equal length have no differing
> characters, then the values are considered equal. Oracle uses nonpadded
> comparison semantics whenever
> one or both values in the comparison have the datatype VARCHAR2 or
> NVARCHAR2.
>
Received on Wed Dec 22 1999 - 14:44:46 CST