REGEX
Syntax
REGEX(<source string>; <regular expression>; <replacement string>[; <string used if the regular expression is not found>])
Description
Using a regular expression, search through a string type column to section the text into groups. Define a replacement string for the result using the index ($) and the sequential group number as a reference. For example, using $1 will be replaced with the first defined group.
Learn more about:
- General information on regular expressions: https://docs.oracle.com/javase/tutorial/essential/regex/index.html
- How to capture groups using regular expressions: https://docs.oracle.com/javase/tutorial/essential/regex/groups.html
- Test regular expressions using a validator tool: http://www.regextester.com/
Examples
| Source string | Regular expression | Replacement expression | REGEX returns |
|---|---|---|---|
| abc | [^b]*(b*)([^b]*) | $2$1 | cb |
| aaaabbbcs | [^b]*(b*)[^b]* | - $1- | -bbb- |
| s | .*(b+).* | -$1- | null |
| server.anywhere.com/usr/bin/amavisd | (^.*[/\\\\])?([^/\\\\]*)$ | $2 | server.anywhere.com/usr/bin |
| server.anywhere.com/usr/bin/amavisd | (^.*[/\\\\])?([^/\\\\]*)$ | $1 | amavisd |
There is a difference in expressions between the Formula Builder and the Formula Bar.
With the Formula Builder, if you are writing regular expressions, you can use normal syntax. A backslash (\) is used to separate each expression.
With the Formula Bar, if you are writing regular expressions you need to include an extra backslash (\) between each expression. This extra backslash between expressions is due to Datameer using the backslash as an escape character.
Before using the REGEX function, think about using REPLACEALL or SUBSTITUTEALL for less complex actions. REGEX can be more process intensive and cause a degrade in performance.