Oracle - Use of Between with letters - Between 'A' and 'C'

Question

this doubt is very basic, however, after reading an answer for a given question I got fairly confused (I don't know why as it is a simple subject).

Consider this basic query:

SELECT * FROM emp WHERE ename BETWEEN ‘A’ AND ‘C’

The employees name returned will be those whose names start with A and B, and the explanation is as follows:

Here, a character column is compared against a string using the BETWEEN operator, which is equivalent to ename >= ‘A’ AND ename <= ‘C’. The name CLARK will not be included in this query, because ‘CLARK’ is > ‘C’.

Why is Clark considered greater than 'C' if in the explanation we have the statement: ename is less than or equal to 'C' ?

Thank you.

Answer 1

Because when you alphabetically sort

Constant Clark C Claude 

you'll get

C Clark Claude Constant 

so

C < Clark < Claude < Constant

See Wikipedia for a more formal explanation, the essence is this (emphasis mine):

To decide which of two strings comes first in alphabetical order, initially their first letters are compared. The string whose first letter appears earlier in the alphabet comes first in alphabetical order. If the first letters are the same, then the second letters are compared, and so on, until the order is decided. (If one string runs out of letters to compare, then it is deemed to come first; for example, "cart" comes before "carthorse".) The result of arranging a set of strings in alphabetical order is that words with the same first letter are grouped together, and within such a group words with the same first two letters are grouped together and so on.