Scalar query examples

Scalar functions transform each input value. These examples use literal values and can run directly here, or be copied into a fresh DuckDB session. Substitute model concepts to apply the same expressions to your data.

Ratios and rounding

select
    round(100.0 * 3 / nullif(8, 0), 1) as conversion_pct,
    100.0 * 3 / nullif(0, 0) as undefined_pct;

The results are 37.5 and NULL. nullif(denominator, 0) makes a missing ratio explicit instead of relying on backend division-by-zero behavior. For a grouped ratio, divide the aggregated numerator by the aggregated denominator; averaging per-row percentages answers a different question.

Normalize before comparing

select
    lower(trim('  ADA@EXAMPLE.COM  ')) as email,
    split('red,green,blue', ',') as tags,
    substring('+14155550123', 3, 3) as area_code;

The results are 'ada@example.com', ['red', 'green', 'blue'], and '415'. Substring positions start at 1, and the third argument is a length. like uses SQL wildcards (% and _); regexp_contains uses a regular expression. Regex replacement scope and capture syntax depend on the backend.

Choose what a missing value means

select
    nullif('', '') as empty_as_null,
    coalesce(nullif('', ''), 'Unknown') as display_label,
    cast('123' as int) as numeric_id;

The results are NULL, 'Unknown', and 123. An empty string is a value; nullif makes it missing before coalesce supplies a display fallback. cast changes the type and can fail for invalid input; it is not a substitute for data validation. See Typing and Validation.

Work with reporting periods

select
    date_trunc('2025-03-15'::date, month) as month_start,
    date_add('2025-03-01'::date, day, 14) as end_date,
    date_diff('2025-03-01'::date, '2025-03-15'::date, day) as elapsed_days;

The results are March 1, March 15, and 14. date_diff(start, end, unit) measures from the first argument to the second; reversing them changes the sign. For monthly grouping across years, use date_trunc(value, month): month(value) alone combines the same month number from different years.