CODEWITHSUNDEEP
sqloracle-database
Vishal
Vishal

Reputation: 279

Questions of string comparison in oracle

I am reading basics of oracle and came across strange statement. I don't know how much true it is.

Statement says

" String value '2' is greater than String value '100'. Character '1' is less than Character '10'. "

Kindly throw some light on above topic. I understand that internally comparison must be happening using ASCII values. I am seeking some good logical explanation.

Upvotes: 3

Views: 4291

Answers (2)

shf301
shf301

Reputation: 31394

You are exactly correct in assuming that they are sorted by ASCII values - this is called an alphabetic sort. The strings are sorted not as numeric values but as text.

The alphabetic sort compares the values position by position. When comparing the string '2' with the string '100' it starts by comparing '2' with '1'. '2' comes after '1' (the ASCII values of '2' is greater than the ASCII value of '1') alphabetically so the comparison stops so '100' will be listed before '2' in an alphabetic sort. This is exactly equivalent as comparing 'b' to 'azz' - since 'a' comes before 'b', 'azz' will be sorted before 'b'.

Your text is pointing this out because this behavior while understandable is non-intuitive. You would expect a sort to place '100' after '2' since 2 < 100, but that is not was the sort does.

Upvotes: 1

lc.
lc.

Reputation: 116458

It means that numbers treated as strings are not sorted in numerical order but in lexical order, the same way words are sorted in a dictionary. That is, characters are compared one at a time from the left side.

In your first example, "2" is greater than "100" because the '2' is compared to the '1' and found to be bigger. Compare this to the ordering of "C" and "BAA" in a dictionary.

In your second example, "1" is less than "10" because the "1" fully matches the "1" in the left side of "10", but the "10" has characters following the match. Therefore it is greater. Again, compare this to the ordering of "B" and "BA" in a dictionary.

Upvotes: 6

Related Questions