How does SQL compare two character strings with = and <, and what varies by engine?

## What a comparison of two strings actually does When SQL evaluates `a = b` or `a < b` for character strings, it does not compare bytes; it compares under a **collation**. A collation is a named rule set covering: - the relative order of characters (which letters and symbols sort before which), - whether case differences matter (case-sensitive vs case-insensitive), - whether accents and other diacritics matter, - how trailing blanks are handled in comparison. The collation comes from the operands: a column has one, a literal has one, and the session or database supplies a default. The standard requires the two operands of a comparison to be coercible to a common collation; if they are not, the comparison is not well defined and engines typically raise an error about mismatched collations. This is why the same predicate can be true on one system and false on another with identical data. The operator is the same; the rule set behind it is not. ## Ordering is lexicographic, not numeric Comparison operators on character strings work position by position under the collation's ordering. That produces the single most reported surprise: ```sql CREATE TABLE parts (part_no VARCHAR(10)); INSERT INTO parts (part_no) VALUES ('9'), ('10'), ('100'); SELECT part_no FROM parts WHERE part_no < '9' ORDER BY part_no; -- '10' and '100': the first character '1' sorts before '9' ``` It is not a bug and no conversion took place — `'10'` really is less than `'9'` as text. The same effect makes version strings, zero-padded codes and numeric identifiers stored as `VARCHAR` sort in an order that looks scrambled. Zero-padding to a fixed width (`'0009'`, `'0010'`) restores numeric-looking order for equal-length values; the real fix is to store numbers in a numeric column. ## Equality is not byte equality Under a case-insensitive collation, `'ANNA' = 'anna'` is true. Under a case-sensitive one it is false. Neither is more correct — it is a property of the collation in effect, and a query that assumes one behaviour is not portable to a system configured the other way. The same applies to accent sensitivity: whether `'resume' = 'résumé'` holds is a collation decision. Trailing blanks are the third variable. The standard describes padding behaviour as a collation attribute (`PAD SPACE` versus `NO PAD`), and fixed-length `CHAR` types are commonly blank-padded on storage while `VARCHAR` is not. The result is that comparing `'abc'` with `'abc '` may be true or false depending on the types and collation involved. Do not encode an assumption about it into application logic; trim on input or compare explicitly trimmed values if the distinction matters. ## Practical consequences - **Uniqueness follows the collation.** If a comparison says `'ANNA'` equals `'anna'`, so does an equality lookup for them. Whether two such values can coexist as distinct entries depends on the same rule set. - **Range filters on text are text ranges.** `WHERE code >= 'A' AND code < 'B'` selects codes starting with `'A'` under most collations, which is a genuinely useful idiom — but only if you have reasoned about the collation's ordering, including how digits, punctuation and case interleave. - **Comparisons across differently-collated columns** may fail outright rather than silently doing something. That error is friendlier than the alternative; read it as a design signal that two columns holding the same kind of value were declared differently. ## Writing portable string predicates 1. Know the collation of the columns you filter on, and state assumptions in comments when a query depends on case behaviour. 2. Do not store numbers as text if you will ever compare them with `<`, `>` or order by them. 3. If case-insensitive matching is a business requirement, make it explicit rather than inherited from a database default — either through a column defined with the appropriate collation or by comparing normalised values consistently on both sides of the predicate. 4. Be careful with trailing blanks at the boundary: normalise on input rather than hoping the comparison ignores them. ## What interviewers listen for The expected answer names the collation as the thing that decides the outcome, produces `'10' < '9'` (or an equivalent example) without hesitation, and states that case sensitivity, accent sensitivity and blank padding are configuration rather than universal SQL behaviour. A candidate who says "string comparison compares bytes and is always case sensitive" is describing one particular configuration and will be surprised by the next database they touch.