TRY_CAST

Overview

TRY_CAST(expression AS type) behaves exactly like CAST, except that when the conversion isn't possible, it returns NULL instead of raising an error. This makes it useful for cleaning messy or semi-structured data where you expect some values not to conform to the target type and don't want a single bad row to fail the whole query.

Copy code
SELECT raw_value, TRY_CAST(raw_value AS INTEGER) AS parsed_int
FROM staging_table;
-- 'abc' -> NULL, '42' -> 42, '' -> NULL

CAST vs TRY_CAST

Copy code
SELECT CAST('abc' AS INTEGER);       -- error: conversion error
SELECT TRY_CAST('abc' AS INTEGER);   -- NULL

Use CAST when you want bad data to be surfaced immediately as an error (fail fast, useful in strict pipelines). Use TRY_CAST when you want to tolerate bad values and handle them downstream, for example filtering them out or flagging them:

Copy code
SELECT *
FROM staging_table
WHERE TRY_CAST(raw_value AS INTEGER) IS NULL
  AND raw_value IS NOT NULL;   -- rows that failed to parse as integers

Availability

TRY_CAST is available in DuckDB as well as several other engines (including Snowflake and Databricks SQL) as an extension to the ANSI SQL standard, which only defines CAST. DuckDB also exposes a more general TRY expression that wraps arbitrary expressions (not just casts) and returns NULL on error.

Practical pattern: validating before casting

A common ELT pattern is to load raw text columns from a source system, then use TRY_CAST in a transformation layer to safely coerce them to typed columns while quarantining rows that fail to parse, rather than letting a single malformed value break an entire batch load.