Regular Expression : Overview

Introduction:

Regular Expression is one of the useful functions of Tableau which can be used to extract specific elements like address, phone numbers, Zip Code, email address from a string of data.

It is also useful for cleaning text data by removing special characters, punctuation or unwanted whitespace.

Here are some commonly used Regular Expression Functions:

Function	Results
REGEXP_MATCH	Returns True or False if the pattern exists
REGEXP_EXTRACT	Pulls out a specific part of the string.
REGEXP_EXTRACT_NTH	Pulls the n-th occurrence of a pattern.
REGEXP_REPLACE	Swaps the pattern with new text

How Do Regular Expressions Work?

There are components that help build the syntax or composition of an expression, much like building a calculation in Tableau using IF-THIS-THEN-THAT type syntax. When built correctly, the underlying algorithm in Tableau will translate the pattern and provide a match or extract the data. Whenever there isn’t a match to the input pattern, Tableau returns Null.

So first we need to understand what builds/supports the syntax. Generally depending upon requirement a regular expression can contain 3 distinct pieces :

1. Regular Expression Metacharacters Classes – A Metacharacter is a character that has special meaning to a computer program, such as a shell interpreter, or a regular expression (REGEX) engine.

   Common Syntax (Metacharacters) :

   • \d: Matches any digit (0-9)

   • \w: Matches any alphanumeric character (letters/numbers)

   • +: Matches one or more of the preceding character

   • ^: Matches the start of a string

   • $: Matches the end of a string

   [ ]: Matches any character inside the brackets

   Note : we can take help of Cheat Sheet available online.

   
   

2. Regular Expression Operators/Quantifiers – These are used to refine the pattern.  For example, how many times should the pattern match be repeated.

   
   

Operator	Description
|	Alternation. A|B matches either A or B.
*	Match 0 or more times. Match as many times as possible.
+	Match 1 or more times. Match as many times as possible.
?	Match zero or one times. Prefer one.
{n}	Match exactly n times
{n,}	Match at least n times. Match as many times as possible.
{n,m}	Match between n and m times. Match as many times as possible, but not more than m.

3. Set Expressions (Character Classes) – These are used to help define specific characters that we want to match. 

Example	Description
[abc]	Match any of the characters a, b or c.
[^abc]	Negation - match any character except a, b, or c.
[A-M]	Range - match any character from A to M.

How to Use it :

Step 1: Go to create calculated field, rename as per your choice.

Step 2: Write the required code & click on ok.

Note : While renaming calculation field Do not use the existing title or name otherwise it will show error in Tableau

Use Case:

• If we have column of product ID like FUR-BO-100012 , below mentioned methods can be used if we want :-

  To extract only "BO":

REGEXP_EXTRACT_NTH([Product ID],'(\w+)-(\w+)-(\d+)',2)

• To extract last 6 digits :

  REGEXP_EXTRACT_NTH([Product ID],'(\w+)-(\w+)-(\d+)',3)

2. If we want to check "FUR " is present in all rows or not, use :

REGEXP_MATCH([Product ID],'FUR')

It will show the result True if it is present and false if it is not present in the data.

3. If we want to replace "FUR " with Furniture, use:

   REGEXP_REPLACE([Product ID],'FUR','Furniture')

Conclusion :

Regular Expression is a powerful tool to visualize data as per the requirement, it makes data analysis easy specially when we want data in a specic format.

Thank You!!