SQL CAST: Convert a Value Without Mistaking Conversion for Validation

The practice task

Inspect fictional imported quantity strings using SQLite integer conversion. Include a leading-zero value, a numeric prefix followed by text, invalid text and NULL to expose the conversion policy.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE imports(id INTEGER, raw TEXT);
INSERT INTO imports VALUES (1,'007'),(2,'12x'),(3,'invalid'),(4,NULL);

SELECT id, raw, CAST(raw AS INTEGER) AS converted
FROM imports ORDER BY id;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The leading-zero string becomes the integer 7. SQLite parses the numeric prefix of 12x as 12 and turns invalid into zero in this exercise. NULL remains NULL. Those results show why merely casting the column does not validate the imported quantity.

A zero produced from invalid text can become indistinguishable from a measured zero if the original value is discarded. Preserve the raw input, classify invalid records separately and apply a validation rule appropriate to the source format before using the converted value in a report.

These permissive outcomes are SQLite-specific. Other products may raise an error or provide separate safe-conversion functions. Do not copy a conversion rule between engines without testing the malformed inputs that matter to your import.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Preserve raw imported values while validating them.
• Do not infer validity from a non-NULL converted result.
• Test leading zeros, mixed text and missing values.