Scenario

A geologist is working with a dataset of rock samples where each description contains a mix of letters and numbers representing the rock's age in millions of years.

Task

Write a Snowflake SQL query that:

1. Selects sample_id and description from {{ ref("rock_samples") }}
2. Extracts the numeric portion from description using a regular expression and aliases it as age
3. Returns an empty string '' for age when no numeric value is present in the description

Schema

rock_samples

Example

rock_samples:

Expected Output:

Note: The age column is returned as a string. For S3 (Limestone), there is no numeric part so age is an empty string '', not NULL.

SELECT
    sample_id,
    description,
    COALESCE(REGEXP_SUBSTR(description, '[0-9]+'), '') AS age
FROM {{ ref("rock_samples") }}

Explanation

Step 1: Extracting digits with REGEXP_SUBSTR

REGEXP_SUBSTR(description, '[0-9]+')

REGEXP_SUBSTR searches a string for a pattern and returns the first match. The pattern [0-9]+ matches one or more consecutive digits. For 'Basalt_450Ma', this returns '450'. When no digits exist (e.g., 'Limestone'), the function returns NULL.

Step 2: Handling missing matches with COALESCE

COALESCE(REGEXP_SUBSTR(description, '[0-9]+'), '')

COALESCE returns the first non-NULL argument. Since REGEXP_SUBSTR returns NULL when no match is found, wrapping it in COALESCE(..., '') converts those NULL results into an empty string. This ensures the age column never contains NULL values.

Step 3: Alternative with CTE

You can wrap the logic in a Common Table Expression for readability:

WITH extracted AS (
    SELECT
        sample_id,
        description,
        COALESCE(REGEXP_SUBSTR(description, '[0-9]+'), '') AS age
    FROM {{ ref("rock_samples") }}
)
SELECT * FROM extracted

For a single extraction like this, the flat query and CTE produce identical results. CTEs become more useful when chaining multiple transformation steps.