I have these values stored as Varchar:
00
0
NEE-01
LAH-01
how is MAX() function determining which is "more" MAX()? Thanks a lot!
The higher value is determined by the alphabetic sorting based on the collation. L comes before N so, NEE-01 would be the "max" for the given data and LAH-01 would be the "min".
It's the same rule that is applied if you use order by some_varchar_column
Character values are compared on the basis of two measures:
Binary or linguistic sorting
Blank-padded or nonpadded comparison semantics
And then further down:
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
(Emphasis mine)
So with the default binary comparison the ASCII values of the characters are taken into account. Which also means that uppercase characters are considered "higher" then lowercase characters.