What happens when a WHERE predicate compares a VARCHAR column to a numeric literal?

middlemust knowfreq 58%

answer

the two sides are not the same type
someone has to convert before comparing
the standard calls them not comparable
engines coerce in different directions
match the literal's type to the column

basics

~20 s

The operands have different types, so something must convert. Standard SQL treats character and numeric values as not comparable; engines that allow the comparison apply their own implicit conversion, so results and errors vary. Compare like with like instead.

solid answer

~50 s

A comparison predicate requires operands of comparable types. Character strings and numbers are not comparable in the standard, so WHERE account_code = 100 on a VARCHAR column is not a well-formed standard comparison. Engines are permissive to different degrees: some coerce the string side to a number, some coerce the number to a string, and some reject the statement. That divergence is the danger — under numeric coercion '0100', ' 100' and '100' all equal 100, so rows you consider distinct collapse together, and a row holding non-numeric text can raise a conversion error mid-scan even though it is irrelevant to the filter. The portable fix is to compare like with like: quote the literal (= '0100') when the column is character, use a typed literal such as DATE '2026-03-05' for dates, and add an explicit CAST only when you have decided which direction the conversion should go.

A comparison predicate requires operands of comparable types. Character strings and numbers are not comparable in the standard, so `WHERE account_code = 100` on a `VARCHAR` column is not a well-formed standard comparison. Engines are permissive to different degrees: some coerce the string side to a number, some coerce the number to a string, and some reject the statement. That divergence is the danger — under numeric coercion `'0100'`, `' 100'` and `'100'` all equal `100`, so rows you consider distinct collapse together, and a row holding non-numeric text can raise a conversion error mid-scan even though it is irrelevant to the filter. The portable fix is to compare like with like: quote the literal (`= '0100'`) when the column is character, use a typed literal such as `DATE '2026-03-05'` for dates, and add an explicit `CAST` only when you have decided which direction the conversion should go.

code

sql · 7 lines

sql

-- account_code is VARCHAR(10) and holds '100', '0100', ' 100', 'N/A'

-- Cross-type: engine decides the conversion; may match 3 rows, 1 row, or error
SELECT * FROM accounts WHERE account_code = 100;

-- Portable: compare like with like
SELECT * FROM accounts WHERE account_code = '0100';

go deeper

for a junior

Know that the literal should match the column's type: quote it for character columns, use DATE '2026-03-05' for dates. Expect to be asked what WHERE code = 100 does when code is VARCHAR.

for a middle

Explain that operands must be comparable, that engines coerce in different directions, and give a concrete surprise such as '0100' matching 100 under numeric conversion. Show the like-with-like rewrite.

for a senior

Bring the operational angle: a coercion converts every row it evaluates, so one non-numeric value turns a working query into a runtime failure. Argue for typing the column correctly rather than patching predicates one at a time.

for a principal

Own it as a schema and portability standard: numbers stored as numbers, typed literals in generated SQL, explicit casts reviewed as deliberate decisions, and a policy that cross-type predicates never reach shared query code.

Comparable types, not identical types

A comparison predicate is operand <comparison operator> operand, and the standard requires the two operands to be comparable: both numeric, both character strings with a common collation, both datetimes of compatible kinds, and so on. Numeric and character are different families and are not mutually comparable. WHERE account_code = 100, where account_code is VARCHAR(10), therefore has no defined standard meaning.

Real engines are more forgiving than the standard, and they are forgiving in different ways. Broadly you will meet three behaviors:

Coerce the character side to a number. The predicate becomes a numeric comparison.
Coerce the numeric side to a character string. The predicate becomes a string comparison.
Reject the statement with a type error and demand an explicit CAST.

Because you cannot know which one you got by reading the query, a cross-type predicate is a portability hazard even before it is a correctness hazard.

Why numeric coercion changes which rows match

Suppose account_code holds '100', '0100', ' 100' and '100.0'. Under numeric coercion every one of those converts to the number 100, so all four rows match = 100. Under string comparison only '100' matches. Those are two completely different result sets from the same query text.

The same asymmetry hits ordering. WHERE part_no > 9 on a character column is meaningless as written; if the engine coerces to a string comparison it compares '9' character by character, and '10' is less than '9'. If it coerces to numbers, '10' is greater. Equality bugs are annoying; range bugs on a coerced column produce results that are wrong in a direction nobody predicts.

Conversion errors on rows you did not care about

A column typed as text usually contains text. If even one row holds 'N/A' or 'A-100', a numeric coercion has to convert that value too, and conversion failure is a runtime error. The query may work for months and then fail the day someone inserts a non-numeric code — and the failing row may have nothing to do with the filter you wrote. This is why "it works today" is not evidence that a cross-type predicate is safe.

Dates and timestamps have the same problem

WHERE created_on = '2026-03-05' compares a date column with a character literal. Most engines interpret the literal as a date, but the accepted formats, and whether the session's date-format setting influences parsing, vary. The portable form is a typed literal:

sql

WHERE created_on = DATE '2026-03-05'

Similarly TIMESTAMP '2026-03-05 14:00:00'. Typed literals say what you mean and remove the parser's discretion.

The fix: compare like with like

The first choice is always to write a literal of the column's own type:

sql

-- account_code VARCHAR(10) storing zero-padded codes
WHERE account_code = '0100'

If the column genuinely stores numbers as text and you need numeric semantics, make the conversion explicit and deliberate, CAST(account_code AS INTEGER) = 100, understanding that you have accepted the error risk on non-numeric rows and that the predicate now tests a computed expression rather than the stored column. If instead the literal is the odd one out — a numeric column compared to '42' — convert the literal, not the column.

The structural fix, when a column holds numbers, is to type it numerically in the first place. A VARCHAR code column that is always compared to numbers is a schema smell.

Equality is not the only operator affected

Everything above applies to <, >, <=, >= and <> as well. In fact ordering comparisons are worse, because a wrong-but-successful conversion produces a plausible ordering that no one questions. <> inherits the same conversion, so a mismatched-type inequality can silently exclude or include rows.

Interview register

A strong answer states the standard rule (operands must be comparable), notes that engines diverge in how they coerce, gives a concrete surprise such as '0100' = 100 being true under numeric coercion, mentions the runtime conversion error on unrelated rows, and lands on "compare like with like, use typed literals, and cast explicitly when you truly must". A weak answer says "SQL handles it automatically" — which is precisely the assumption that produces the wrong result set.

## Comparable types, not identical types A comparison predicate is `operand <comparison operator> operand`, and the standard requires the two operands to be *comparable*: both numeric, both character strings with a common collation, both datetimes of compatible kinds, and so on. Numeric and character are different families and are not mutually comparable. `WHERE account_code = 100`, where `account_code` is `VARCHAR(10)`, therefore has no defined standard meaning. Real engines are more forgiving than the standard, and they are forgiving in *different ways*. Broadly you will meet three behaviors: - **Coerce the character side to a number.** The predicate becomes a numeric comparison. - **Coerce the numeric side to a character string.** The predicate becomes a string comparison. - **Reject the statement** with a type error and demand an explicit `CAST`. Because you cannot know which one you got by reading the query, a cross-type predicate is a portability hazard even before it is a correctness hazard. ## Why numeric coercion changes which rows match Suppose `account_code` holds `'100'`, `'0100'`, `' 100'` and `'100.0'`. Under numeric coercion every one of those converts to the number 100, so all four rows match `= 100`. Under string comparison only `'100'` matches. Those are two completely different result sets from the same query text. The same asymmetry hits ordering. `WHERE part_no > 9` on a character column is meaningless as written; if the engine coerces to a string comparison it compares `'9'` character by character, and `'10'` is *less* than `'9'`. If it coerces to numbers, `'10'` is greater. Equality bugs are annoying; range bugs on a coerced column produce results that are wrong in a direction nobody predicts. ## Conversion errors on rows you did not care about A column typed as text usually contains text. If even one row holds `'N/A'` or `'A-100'`, a numeric coercion has to convert that value too, and conversion failure is a runtime error. The query may work for months and then fail the day someone inserts a non-numeric code — and the failing row may have nothing to do with the filter you wrote. This is why "it works today" is not evidence that a cross-type predicate is safe. ## Dates and timestamps have the same problem `WHERE created_on = '2026-03-05'` compares a date column with a character literal. Most engines interpret the literal as a date, but the accepted formats, and whether the session's date-format setting influences parsing, vary. The portable form is a typed literal: ```sql WHERE created_on = DATE '2026-03-05' ``` Similarly `TIMESTAMP '2026-03-05 14:00:00'`. Typed literals say what you mean and remove the parser's discretion. ## The fix: compare like with like The first choice is always to write a literal of the column's own type: ```sql -- account_code VARCHAR(10) storing zero-padded codes WHERE account_code = '0100' ``` If the column genuinely stores numbers as text and you need numeric semantics, make the conversion explicit and deliberate, `CAST(account_code AS INTEGER) = 100`, understanding that you have accepted the error risk on non-numeric rows and that the predicate now tests a computed expression rather than the stored column. If instead the *literal* is the odd one out — a numeric column compared to `'42'` — convert the literal, not the column. The structural fix, when a column holds numbers, is to type it numerically in the first place. A `VARCHAR` code column that is always compared to numbers is a schema smell. ## Equality is not the only operator affected Everything above applies to `<`, `>`, `<=`, `>=` and `<>` as well. In fact ordering comparisons are worse, because a wrong-but-successful conversion produces a plausible ordering that no one questions. `<>` inherits the same conversion, so a mismatched-type inequality can silently exclude or include rows. ## Interview register A strong answer states the standard rule (operands must be comparable), notes that engines diverge in how they coerce, gives a concrete surprise such as `'0100' = 100` being true under numeric coercion, mentions the runtime conversion error on unrelated rows, and lands on "compare like with like, use typed literals, and cast explicitly when you truly must". A weak answer says "SQL handles it automatically" — which is precisely the assumption that produces the wrong result set.

Under numeric coercion, which stored values would WHERE account_code = 100 match on a VARCHAR column?
Every value that converts to the number 100: `'100'`, `'0100'`, `' 100'` and `'100.0'` all become 100, so all four rows match. Under a string comparison only the exact text `'100'` matches. The same query text therefore yields different result sets on different engines, which is why the predicate should be written with a character literal.
A cross-type predicate has run fine for a year and now throws a conversion error. What changed?
Almost certainly the data, not the query. Numeric coercion has to convert every row the predicate is evaluated against, so the first non-numeric value inserted into the text column — `'N/A'`, `'A-100'` — makes the conversion fail at runtime. The failing row need not have anything to do with the filter; the fix is to stop coercing, not to clean one row.
When is an explicit CAST the right answer rather than changing the literal?
When the column genuinely stores numbers as text and you need numeric semantics — for example ordering codes numerically. Then `CAST(col AS INTEGER)` states the intent, but you accept a runtime error on non-numeric rows and you are now testing a computed expression rather than the stored value. If only the literal is mistyped, convert the literal instead; if the column is always used numerically, retype the column.

saying these in an interview costs you the question

Assumes SQL converts types automatically and safely everywhere
Believes a mismatched-type comparison always errors out
Thinks '0100' = 100 is false regardless of engine
Wraps the column in CAST as the default fix without considering the literal
Treats a VARCHAR column holding only digits as interchangeable with a numeric column

What happens when a WHERE predicate compares a VARCHAR column to a numeric literal?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Comparable types, not identical types

Why numeric coercion changes which rows match

Conversion errors on rows you did not care about

Dates and timestamps have the same problem

The fix: compare like with like

Equality is not the only operator affected

Interview register

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions7 questions

▾related topics4 topics

Comparable types, not identical types

Why numeric coercion changes which rows match

Conversion errors on rows you did not care about

Dates and timestamps have the same problem

The fix: compare like with like

Equality is not the only operator affected

Interview register

answer

go deeper

context