oracle 中字符串的比较大小,Oracle 字符串比较大小规则

字符串的比较， 将字符转换成 ASCII 对应的数字再 比较其大小

select  ascii('100D1064'),ascii('100000') ,  ascii('199999')   from dual;

https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements002.htm#SQLRF30027

Character Values

Character values are compared on the basis of two measures:

Binary or linguistic sorting

Blank-padded or nonpadded comparison semantics

The following subsections describe the two measures.

Binary and Linguistic Comparisons

In binary comparison, which is the default, Oracle compares character strings according to the concatenated value of the numeric codes of the characters in the database character set. One character is greater than another if it has a greater numeric value than the other in the character set. Oracle considers blanks to be less than any character, which is true in most character sets.

These are some common character sets:

7-bit ASCII (American Standard Code for Information Interchange)

EBCDIC Code (Extended Binary Coded Decimal Interchange Code)

ISO 8859/1 (International Organization for Standardization)

JEUC Japan Extended UNIX

Linguistic comparison is useful if the binary sequence of numeric codes does not match the linguistic sequence of the characters you are comparing. Linguistic comparison is used if the NLS_SORTparameter has a setting other than BINARY and the NLS_COMP parameter is set to LINGUISTIC. In linguistic sorting, all SQL sorting and comparison are based on the linguistic rule specified by NLS_SORT.

See Also:

Oracle Database Globalization Support Guide for more information about linguistic sorting

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'

Portions of the ASCII and EBCDIC character sets appear in Table 3-8 and Table 3-9. Uppercase and lowercase letters are not equivalent. The numeric values for the characters of a character set may not match the linguistic sequence for a particular language.

Table 3-8 ASCII Character Set

Symbol

Decimal value

Symbol

Decimal value

blank

32

;

59

!

33

<

60

"

34

=

61

#

35

>

62

$

36

?

63

%

37

@

64

&

38

A-Z

65-90

'

39

[

91

(

40

\

92

)

41

]

93

*

42

^

94

+

43

_

95

,

44

'

96

-

45

a-z

97-122

.

46

{

123

/

47

|

124

0-9

48-57

}

125

:

58

~

126

Table 3-9 EBCDIC Character Set

Symbol

Decimal value

Symbol

Decimal value

blank

64

%

108

¢

74

_

109

.

75

>

110

<

76

?

111

(

77

:

122

+

78

#

123

|

79

@

124

&

80

'

125

!

90

=

126

$

91

"

127

*

92

a-i

129-137

)

93

j-r

145-153

;

94

s-z

162-169

ÿ

95

A-I

193-201

-

96

J-R

209-217

/

97

S-Z

226-233