REGEXP_EXTRACT / REGEXP_SUBSTR

Category: String Functions

Platform Support:

✅ Snowflake (REGEXP_SUBSTR) | ✅ BigQuery (REGEXP_EXTRACT) | ✅ Databricks (REGEXP_EXTRACT)

Description

Extracts substrings that match a regular expression pattern. Essential for parsing unstructured text, extracting domains from emails, parsing log files, extracting codes from descriptions, and working with semi-structured string data.

Syntax by Platform

Snowflake:

REGEXP_SUBSTR(
    string, 
    pattern, 
    position,      -- starting position (default 1)
    occurrence,    -- which match to return (default 1)
    parameters,    -- 'e' for extract, 'i' for case-insensitive
    group          -- which capture group (default 0)
)

BigQuery:

REGEXP_EXTRACT(string, pattern)
-- Returns first capturing group
-- Use REGEXP_EXTRACT_ALL for all matches

Databricks:

REGEXP_EXTRACT(
    string, 
    pattern, 
    group_index    -- 0 for full match, 1+ for capture groups
)

Platform-Specific Notes

Snowflake:

• Function name is REGEXP_SUBSTR (not EXTRACT)
• Most flexible with position, occurrence, and parameters
• Use 'e' parameter to enable capture groups
• Group 0 returns full match, 1+ returns capture groups

BigQuery:

• Use raw string prefix r'...' for regex patterns
• Returns only the first capturing group
• REGEXP_EXTRACT_ALL returns array of all matches
• REGEXP_CONTAINS checks if pattern exists

Databricks:

• Third parameter specifies capture group (0-based)
• Group 0 returns entire match
• Compatible with Java regex syntax

Example 1: Extract Domain from Email

Snowflake:

SELECT 
    email,
    REGEXP_SUBSTR(email, '@(.+)$', 1, 1, 'e', 1) as domain
FROM users;

BigQuery:

SELECT 
    email,
    REGEXP_EXTRACT(email, r'@(.+)$') as domain
FROM users;

Databricks:

SELECT 
    email,
    REGEXP_EXTRACT(email, '@(.+)$', 1) as domain
FROM users;

Sample Data (users table):

Result (all platforms):

Example 2: Extract Product Code from Description

Snowflake:

SELECT 
    description,
    REGEXP_SUBSTR(description, 'SKU-([A-Z0-9]+)', 1, 1, 'e', 1) as product_code,
    REGEXP_SUBSTR(description, '([0-9]+)GB', 1, 1, 'e', 1) as storage_gb
FROM products;

BigQuery:

SELECT 
    description,
    REGEXP_EXTRACT(description, r'SKU-([A-Z0-9]+)') as product_code,
    REGEXP_EXTRACT(description, r'([0-9]+)GB') as storage_gb
FROM products;

Databricks:

SELECT 
    description,
    REGEXP_EXTRACT(description, 'SKU-([A-Z0-9]+)', 1) as product_code,
    REGEXP_EXTRACT(description, '([0-9]+)GB', 1) as storage_gb
FROM products;

Sample Data (products table):

Result:

Example 3: Extract Phone Number Components

All Platforms:

-- Extract area code from phone number
SELECT 
    phone_number,
    -- Pattern: (123) 456-7890 or 123-456-7890
    REGEXP_EXTRACT(phone_number, '[(]?([0-9]{3})[)]?', 1) as area_code,
    REGEXP_EXTRACT(phone_number, '[0-9]{3}[-.]([0-9]{3})', 1) as exchange,
    REGEXP_EXTRACT(phone_number, '[0-9]{3}[-.]([0-9]{4})', 1) as line_number
FROM contacts;

Sample Data (contacts table):

Result (BigQuery/Databricks syntax):

Example 4: Extract Multiple Matches (BigQuery)

BigQuery Only:

SELECT 
    text,
    REGEXP_EXTRACT_ALL(text, r'#(\w+)') as hashtags,
    ARRAY_LENGTH(REGEXP_EXTRACT_ALL(text, r'#(\w+)')) as hashtag_count
FROM social_posts;

Sample Data (social_posts table):

Result:

Common Regex Patterns

Related Functions

Snowflake:

• REGEXP_REPLACE - Replace text matching pattern
• REGEXP_INSTR - Find position of pattern
• REGEXP_COUNT - Count pattern occurrences

BigQuery:

• REGEXP_REPLACE - Replace text matching pattern
• REGEXP_CONTAINS - Check if pattern exists (returns boolean)
• REGEXP_EXTRACT_ALL - Extract all matches as array

Databricks:

• REGEXP_REPLACE - Replace text matching pattern
• REGEXP_LIKE - Check if string matches pattern

Performance Considerations

• Regex operations can be expensive on large datasets
• Pre-filter data before applying regex when possible
• Cache commonly used patterns
• Consider simpler string functions (SUBSTRING, SPLIT) when regex isn't needed
• In BigQuery, use REGEXP_CONTAINS for existence checks (faster than EXTRACT)

Best Practices

1. Test patterns thoroughly - Use online regex testers
2. Use raw strings in BigQuery - Prefix with r'...'
3. Handle NULLs - Wrap in COALESCE if needed
4. Document complex patterns - Add comments explaining the regex
5. Use capture groups wisely - Only capture what you need
6. Consider case sensitivity - Use flags appropriately