Regexextract

⚠️ regexextract is unavailable for MongoDB, SQLite, and SQL Server. For Druid, regexextract is only available for the Druid-JDBC driver.

regexextract uses regular expressions (regex) to get a specific part of your text.

regexextract is ideal for text that has little to no structure, like URLs or freeform survey responses. If you’re working with strings in predictable formats like SKU numbers, IDs, or other types of codes, check out the simpler substring expression instead.

Use regexextract to create custom columns with shorter, more readable labels for things like:

• filter dropdown menus,
• chart labels, or
• embedding parameters.

Let’s say that you have web data with a lot of different URLs, and you want to map each URL to a shorter, more readable campaign name.

You can create a custom column Campaign Name with the expression:

regexextract([URL], "^[^?#]+\?utm_campaign=(.*)")
    
                
Copy
    
    
                
Copied
    

Here, the regex pattern ^[^?#]+\? matches all valid URL strings. You can replace utm_campaign= with whatever query parameter you like. At the end of the regex pattern, the capturing group (.*) gets all of the characters that appear after the query parameter utm_campaign=.

Now, you can use Campaign Name in places where you need clean labels, such as filter dropdown menus, charts, and embedding parameters.

regexextract is unavailable for MongoDB, SQLite, and SQL Server. For Druid, regexextract is only available for the Druid-JDBC driver.

Regex can be a dark art. You have been warned.

This section covers functions and formulas that work the same way as the Metabase regexextract expression, with notes on how to choose the best option for your use case.

Metabase expressions

• substring

Other tools

• SQL
• Spreadsheets
• Python

Use substring when you want to search text that has a consistent format (the same number of characters, and the same relative order of those characters).

For example, you wouldn’t be able to use substring to get the query parameter from the URL sample data, because the URL paths and the parameter names both have variable lengths.

But if you wanted to pull out everything after https://www. and before .com, you could do that with either:

substring([URL], 13, 8)
    
                
Copy
    
    
                
Copied
    

or

regexextract([URL], "^(?:https?:\/\/)?(?:[^@\/\n]+@)?(?:www\.)?([^:\/.\n]+)")
    
                
Copy
    
    
                
Copied
    

When you run a question using the notebook editor, Metabase will convert your graphical query settings (filters, summaries, etc.) into a query, and run that query against your database to get your results.

If our sample data is stored in a PostgreSQL database:

SELECT
    url,
    SUBSTRING(url, '^[^?#]+\?utm_campaign=(.*)') AS campaign_name
FROM follow_the_white_rabbit
    
                
Copy
    
    
                
Copied
    

is equivalent to the Metabase regexextract expression:

regexextract([URL], "^[^?#]+\?utm_campaign=(.*)")
    
                
Copy
    
    
                
Copied
    

If our sample data is in a spreadsheet where “URL” is in column A, the spreadsheet function

regexextract(A2, "^[^?#]+\?utm_campaign=(.*)")
    
                
Copy
    
    
                
Copied
    

uses pretty much the same syntax as the Metabase expression:

regexextract([URL], "^[^?#]+\?utm_campaign=(.*)")
    
                
Copy
    
    
                
Copied
    

Assuming the sample data is in a dataframe column called df,

df['Campaign Name'] = df['URL'].str.extract(r'^[^?#]+\?utm_campaign=(.*)')
    
                
Copy
    
    
                
Copied
    

does the same thing as the Metabase regexextract expression:

regexextract([URL], "^[^?#]+\?utm_campaign=(.*)")
    
                
Copy
    
    
                
Copied
    

• Custom expressions documentation
• Custom expressions tutorial