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.
regexextract to create custom columns with shorter, more readable labels for things like:
- filter dropdown menus,
- chart labels, or
- embedding parameters.
|Gets a specific part of your text using a regular expression.||“extract”|
Searching and cleaning text
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:
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
Accepted data types
|Data type||Works with
Regex can be a dark art. You have been warned.
regexextract is not supported on H2 (including the Metabase Sample Database), SQL Server, and SQLite.
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.
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)
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
is equivalent to the Metabase
If our sample data is in a spreadsheet where “URL” is in column A, the spreadsheet function
uses pretty much the same syntax as the Metabase expression:
Assuming the sample data is in a dataframe column called
df['Campaign Name'] = df['URL'].str.extract(r'^[^?#]+\?utm_campaign=(.*)')
does the same thing as the Metabase