Common mistake

SQL: Why Numbers Stored as Text Sort Wrong

Text compares one character at a time, so '9' sorts above '75'. Cast the column to a number before you sort, compare, or take a MAX.

The symptom

You sort a column of quantities from largest to smallest, and the top of the list reads 90, 9, 8, 75, 60. The two biggest values, 200 and 120, aren't in the top five at all. No error, and every value is really in the table.

Why it happens

The column holds text that looks like numbers. Text sorts the way a dictionary does: compare the first character, and only look at the second if the first ones match. '9' beats '7', so '9' sorts above '75'. '200' starts with '2', so it lands below everything that starts with a higher digit.

It usually arrives with an import: a CSV column that had one stray value like n/a or 1,200, a spreadsheet export, or a JSON field that quoted its numbers. The loader falls back to text for the whole column, and nothing looks wrong until you sort.

The fix

Cast to a number before you sort. Garden Shop stores quantity_on_hand as an integer, so both queries below first turn it into text to stand in for a bad import. Only the ORDER BY differs.

Wrong: sorted as text

with imported as (
  select product_name,
         cast(quantity_on_hand as varchar) as quantity_on_hand
  from products
)
select product_name, quantity_on_hand
from imported
order by quantity_on_hand desc
limit 5

Right: cast to a number

with imported as (
  select product_name,
         cast(quantity_on_hand as varchar) as quantity_on_hand
  from products
)
select product_name, quantity_on_hand
from imported
order by cast(quantity_on_hand as integer) desc
limit 5

The cast restores the real order: 200, 120, 90, 75, 60. The displayed values are still text, which is fine for a report. Better still, fix the type where the data comes in, in a staging view or when you load the file, so every later query gets numbers without having to remember.

Sorting isn't the only thing that breaks

max() over the text column returns '90', not 200. A filter such as quantity > '50' keeps '6', '8', and '9', and drops '120' and '200'. DuckDB refuses quantity > 50 outright ("Cannot compare values of type VARCHAR and type INTEGER_LITERAL"), which at least tells you something is wrong; some engines convert silently instead.

SUM and AVG refuse text

Totals fail outright instead of coming out wrong. DuckDB reports No function matches the given name and argument types 'sum(VARCHAR)', PostgreSQL function sum(character varying) does not exist, and SQL Server Operand data type varchar is invalid for sum operator. The fix is the same cast: sum(cast(quantity as integer)). MySQL is the exception: it converts the text to a number on its own, reading only the leading digits, so '1,200' adds 1 and 'n/a' adds 0, with only a warning.

When some values won't cast

cast('1,200' as integer) fails the whole query. In DuckDB, try_cast returns NULL for a value it can't convert, so you can find the bad rows with where try_cast(quantity as integer) is null and clean them. PostgreSQL has no try_cast; clean the text first, for example with replace(quantity, ',', ''). The error itself is explained on Could not convert string.