I was working with a client recently and needed to do find and replace in a Tableau calculation. While Tableau does have a native FIND() function, it just finds if a character string exists and tells you what position that string starts at. Not particularly helpful when it comes to replacing.
We can use this school district data for our find and replace example:
Let’s imagine we want these district names to be simpler like “Eatonville”, “Edmonds”, “Enumclaw”. We can’t simply delete the last 14 characters because look at “North Thurston Public Schools”. It doesn’t follow the same rules as the previous districts. I ran into these inconsistencies with my client a lot.
Our best option was to isolate words like “School”, “District”, “Public” and “Schools” and replace those with a space. The trick to pulling all of this off is the function REGEXP_REPLACE().
Here’s what a calculation would look like that replaces all the unwanted words from above:
Here’s what’s happening in the REGEXP expression.
First, we define our field of interest ([District]).
Next, we create a list of (case sensitive) words we’d like to replace, separated by | delimiters. Notice the . after “School”. That is a wildcard value so if another character shows up after school, that word is replaced. Because of the way regular expressions work, “School” is also replaced so we kill two birds with one stone.
Next, we indicate what we want one of the previous words replaced with. In this case it’s a space.
Last, we wrap the whole argument in TRIM() to remove trailing spaces.
Here’s our new output in all its glory:
Want help with your own Tableau calculation questions? Sign up for an office hour and we’ll help you!