When applying the BETWEEN operator on TEXT values, how are values compared?


#1

Question

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 TEXT values?

Answer

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.

Example

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
*/