Every column has a type, and many problems come from data stored as the wrong one. Imports often load everything as text: an age column holding '34', ' 41' and 'n/a'.
CAST(value AS type) converts: CAST(age_text AS INTEGER). PostgreSQL and the database here also accept the short form age_text::INTEGER. A value that can't convert, such as 'n/a', makes the whole query fail. TRY_CAST returns NULL instead, so one bad value doesn't stop the report. SQL Server and Snowflake have TRY_CAST too; BigQuery calls it SAFE_CAST.
Types change behavior even when no error appears:
- Text sorts character by character, so
'100' comes before '23'. Sort numbers stored as text after casting them.
- Whole number divided by whole number drops the decimals in PostgreSQL, SQL Server and here:
7 / 2 is 3. Cast one side to DECIMAL, or multiply by 1.0 first.
- Dates stored as text compare as text. Cast them with
CAST(x AS DATE), which reads the YYYY-MM-DD form.