A column doesn't have to be declared TEXT for numbers to end up stored as text inside it — a CSV import, a JSON API response, or a spreadsheet export will happily hand a database the string "10" instead of the number 10, and SQLite's relaxed typing will store it exactly as given. Every value still looks like a number when you eyeball the table. The problem only shows up the moment something tries to sort or compare those values, because a database sorting text doesn't count — it compares characters, left to right, the same way it would sort words in a dictionary.
Verified by actually running this against SQLite — not hand-computed. A small inventory table where quantity was imported from a CSV and landed as TEXT instead of INTEGER.
CREATE TABLE inventory(
product_name TEXT,
quantity TEXT
);
INSERT INTO inventory VALUES
('Notebook','25'),
('Pen','5'),
('Stapler','100'),
('Eraser','2'),
('Marker','10');| product_name | quantity |
|---|---|
| Notebook | '25' |
| Pen | '5' |
| Stapler | '100' |
| Eraser | '2' |
| Marker | '10' |
Every value here is a perfectly ordinary-looking number. Nothing about glancing at this table suggests a problem — the trouble is entirely in the column's declared type, which doesn't show up in a normal SELECT *.
SELECT *
FROM inventory
ORDER BY quantity ASC;| product_name | quantity |
|---|---|
| Marker | '10' |
| Stapler | '100' |
| Eraser | '2' |
| Notebook | '25' |
| Pen | '5' |
The lowest quantity on the shelf, 2, ends up third. The highest, 100, ends up second. This isn't close to sorted by quantity in any numeric sense — 10 and 100 jump to the front simply because they start with the character '1', which sorts before '2', '5', and '9'.
quantity is declared TEXT, so SQLite compares its values the way it compares any text: character by character, left to right, using each character's code point — exactly like sorting words alphabetically. '100' and '2' are compared by looking at their first characters first: '1' versus '2'. '1' comes first, so '100' sorts before '2', no matter what the rest of either string says. The comparison never gets far enough to notice that one number is fifty times larger than the other, because it was never told these were numbers in the first place.
SELECT *
FROM inventory
WHERE quantity > '8';(0 rows)Three products — Marker (10), Notebook (25), Stapler (100) — clearly have more than 8 units in stock. This query returns nothing. '10', '25', and '100' all start with '1' or '2', both of which sort before '8' as characters, so none of them count as "greater than" '8' in a text comparison — even though every one of them is numerically much larger.
SELECT *
FROM inventory
ORDER BY CAST(quantity AS INTEGER) ASC;| product_name | quantity |
|---|---|
| Eraser | '2' |
| Pen | '5' |
| Marker | '10' |
| Notebook | '25' |
| Stapler | '100' |
CAST(quantity AS INTEGER) converts each value to a real number before the comparison runs, so ORDER BY sorts 2, 5, 10, 25, 100 the way anyone would expect. The same fix applies directly to the WHERE version: WHERE CAST(quantity AS INTEGER) > 8 correctly returns Marker, Notebook, and Stapler. CAST is a query-time patch — the more durable fix is correcting the column's type at the source (the import script, the table definition) so nothing downstream has to remember to cast it every time.
Nobody sits down and deliberately declares a quantity column as TEXT. It happens through the side doors: a CSV loader that treats every column as text unless told otherwise, a JSON API that serializes all its numbers as quoted strings, a spreadsheet export where a "Format Cells as Text" setting leaked into a column, or a user-facing form field that was never validated as numeric before being saved. By the time the data lands in a table, it looks completely ordinary — which is exactly why this bug tends to survive for a long time before anyone notices the sort order is wrong.
| Symptom | Likely cause | Fix |
|---|---|---|
| ORDER BY puts 10 before 2 | Numeric-looking column is actually TEXT | ORDER BY CAST(col AS INTEGER) |
| WHERE col > 'N' returns too few or zero rows | Comparing TEXT lexicographically instead of numerically | WHERE CAST(col AS INTEGER) > N |
| Long-term fix | Import/ingestion never set the right column type | Correct the type at the source table or the load script |
This trap is quiet because the data never looks wrong — every value really is the number it claims to be, just spelled out as text instead of stored as one. The only way to catch it ahead of time is checking a column's actual declared type before trusting ORDER BY or a numeric WHERE comparison on it, especially on any column that came from a CSV, an API, or a spreadsheet rather than being typed by hand.