Scenario

You are an analyst at a retail company reviewing product listings where some items have discount information embedded in their descriptions.

Task

Write a Snowflake SQL query that:

1. Selects all existing columns from {{ ref("products") }}
2. Extracts the numeric percentage value from description where the pattern [N% off] appears (inside square brackets)
3. Converts the extracted number to a decimal by dividing by 100
4. Returns 0 (not NULL) when no discount pattern is found
5. Aliases the result as discount

Schema

products

Example

products:

Expected Output:

Note: The discount is always an integer percentage inside [N% off]. Divide the extracted number by 100 to get the decimal. Rows with no bracketed discount return 0, not NULL.

WITH extracted AS (
    SELECT
        store_id,
        product_name,
        category,
        units_sold,
        description,
        COALESCE(
            TRY_CAST(
                REGEXP_SUBSTR(description, '\\[(\\d+)% off\\]', 1, 1, 'e', 1)
                AS FLOAT
            ) / 100,
            0
        ) AS discount
    FROM {{ ref("products") }}
)
SELECT * FROM extracted

Explanation

Step 1: Extract the discount number with REGEXP_SUBSTR

REGEXP_SUBSTR(description, '\\[(\\d+)% off\\]', 1, 1, 'e', 1)

REGEXP_SUBSTR searches description for the pattern [N% off]. The \\[ and \\] match literal square brackets. (\\d+) is a capture group that grabs the numeric digits. The parameters 1, 1, 'e', 1 mean: start at position 1, find the 1st occurrence, use the 'e' flag to enable extraction of subgroups, and return capture group 1 (the digits). When no match exists, the function returns NULL.

Step 2: Cast to a numeric type with TRY_CAST

TRY_CAST(... AS FLOAT) / 100

The extracted value is a string (e.g., '15'). TRY_CAST converts it to FLOAT so you can divide by 100 to produce the decimal (e.g., 0.15). Unlike CAST, TRY_CAST returns NULL instead of raising an error when the conversion fails, making it safer for mixed data.

Step 3: Handle missing discounts with COALESCE

COALESCE(..., 0)

When REGEXP_SUBSTR finds no match it returns NULL, and TRY_CAST(NULL) remains NULL. Wrapping the entire expression in COALESCE(..., 0) replaces that NULL with 0 so every row has a valid numeric discount.

Alternative: Using CAST instead of TRY_CAST

COALESCE(
    CAST(
        REGEXP_SUBSTR(description, '\\[(\\d+)% off\\]', 1, 1, 'e', 1)
        AS FLOAT
    ) / 100,
    0
) AS discount

Since the regex only captures digits, CAST would also work here because the extracted value is always a valid number (or NULL). TRY_CAST is the defensive choice when you cannot guarantee the captured text will parse cleanly.