In SQL, when applying the
BETWEEN operator on a range of
TEXT values, each value must be compared somehow to be ordered correctly. What kind of comparison is done on
In most programming languages, including SQLite and Python,
TEXT or string values are compared based on their lexicographical ordering, and when using the
BETWEEN operator for a range of
TEXT values in SQL, the values will be sorted in this way.
Lexicographical ordering is basically the ordering you would find words in a dictionary. If we had two words, they would be compared starting from their first letter, second letter, and so on, until we find a non-matching letter. The word which has the letter that comes first in the alphabet would ultimately be sorted to come first in this lexicographical ordering.
If two words have different lengths, but match up to the last letter of the shorter word, the shorter word will appear first in the ordering.
A = "Alien" B = "Aliens" C = "Alike" /* Because A and B share the same sequence of characters up to the last character of A, which is shorter, A < B. Also, because "k" comes after "e" in the alphabet, C will come last in the ordering of these 3 words. A < B < C */