oracle varchar 比较,oracle对char 和varchar 类型如何比较

oracle对char 和varchar 类型如何比较

这个问题源于itpub的一个提问

详细查看帖子

--先引用官方的文档里的解释

Blank-Padded and Nonpadded Comparison Semantics

With blank-padded semantics, if the two values have different

lengths, then Oracle

first adds blanks to the end of the shorter one so their lengths

are equal. Oracle then

compares the values character by character up to the first

character that differs. The

value with the greater character in the first differing position is

considered greater. If

two values have no differing characters, then they are considered

equal. This rule

means that two values are equal if they differ only in the number

of trailing blanks.

Oracle uses blank-padded comparison semantics only when both values

in the

comparison are either expressions of data type CHAR, NCHAR, text

literals, or values

returned by the USER function.

With nonpadded 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, then 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 data type VARCHAR2 or NVARCHAR2.

The results of comparing two character values using different

comparison semantics

may vary. The table that follows shows the results of comparing

five pairs of character

values using each comparison semantic. Usually, the results of

blank-padded and

nonpadded comparisons are the same. The last comparison in the

table illustrates the

differences between the blank-padded and nonpadded comparison

semantics.

Blank-Padded Nonpadded

'ac' > 'ab' 'ac' > 'ab'

'ab' > 'a ' 'ab' >

'a '

'ab' > 'a' 'ab' > 'a'

'ab' = 'ab' 'ab' = 'ab'

'a ' = 'a'

'a '

> 'a'

举个例子

TEST@ EARTH>create table test(name_char char(2),

name_varchar varchar(2));

表已创建。

TEST@ EARTH>insert into test values('a','a');

已创建 1 行。

TEST@ EARTH>commit;

提交完成。

TEST@ EARTH>select * from test where

name_char='a ';

NAME NAME

---- ----

a a --char类型比较的时候 增加空格，让两者相等

TEST@ EARTH>select * from test where

name_varchar='a ';