Is simple find-and-replace not enough for your data preparation needs? Face it, swapping out one word or character in millions of database entries can get tedious and repetitive. But now, Oracle Analytics Data Prepare features a Regular Expression Replace function that brings great new powerful abilities with pattern matching. We will see what it has got to offer in this blog.
First, take a quick look at the video which explains briefly about this feature.
Oracle Analytics now allows users to utilize the full potential of Regular Expressions (also known as Regex) in pattern matching of column text in Oracle Analytics Data Preparation or Data Set editor. Before we jump into the feature, let's recap the basics of regular expressions (regex).
Subscribe to the Oracle Analytics Advantage blog and get the latest posts sent to your inbox
Regular Expressions in a Nutshell
The Regular Expressions feature is a powerful tool for matching patterns. A pattern can be a word or a specific character or sequence of characters, occurring once or multiple times. Regex can also help you find words occurring in a specific format, like an email address. We will only address some of the useful Regex patterns in this blog entry, which will still help you kick-start Regex Replace in Oracle Analytics Data Preparation.
For starters, let's see some of the commonly used Regex patterns. Let's start with a pattern to match one or more digits. The '\d+' expression can be used to find the digits from 0-9 anywhere in the string. The backslash '\' is used as escape character, 'd' is the shorthand code for digits and '+' sign denotes one or more occurrence. Other useful patterns are:
w Any alphanumeric character
W Any non-alphanumeric character
d Any digit
D Any non-digit character
. Any character
* Zero or more repetitions
+ One or more repetitions
? Optional character
Character Classes or Character Sets
When you want to match only one out of several characters, Character classes or Character-sets can be used. For example, to match an 'a' or 'e,' we can use [ae]. In this example, we see the pattern 'gr[ae]y' matches gray and grey but not Grey nor graay.
It is also possible to specify a range with  brackets. For example, [0-9] matches a digit from 0-9, [a-z] matches a letter from a to z. Multiple character ranges can also be used. For Example, [A-Z0-9] matches an uppercase alphabet followed by a digit. Remember, the patterns are case-sensitive.
In Regex, a group of characters can be defined for capturing and processing the data. Let's do this with an example. Consider the text 'ABSD0028' which has two parts – alphabets(ABSD) and digits(0028). If we have to write a pattern to split the text into two parts, we can do so by grouping the characters with parenthesis and each group can be captured using back-references. Here is our pattern.
Now, having learned about the grouping, we must know how useful they are in a real-world scenario. The main purpose of grouping in regular expressions is to use one or more groups in the output. Let's consider the above example. We grouped the text into two parts. We can refer these parts using back-references and construct a new text. Naming of the back-reference parameters might vary depending upon the platform/programming language on which Regex engine works.
In Oracle Analytics, back-reference parameters are denoted by a '$' sign followed by the order of occurrence of the group. For example, in the pattern ([A-Z]+)(\d+), '([A-Z]+)' will be referred as $1 and '(\d+)' will be referred as $2.
Alternation is the 'OR' operator in regular expression. To match red or green or blue, use red|green|blue. To match 'red color' or 'blue color' use grouping, for example, (red|blue) color.
Constructing a Pattern
Now, let's construct a pattern which we could use in Regex Replace. Consider extracting email handle from an email address. Email identifiers are in the following format: <some_text>@<org_name>.<com>. Let's split this format into three parts. The first part is the email handle. The second part is the '@' symbol and the third part is the domain. The first part could include alphanumeric characters with special characters like '_' and '.'. So, the pattern for this part would be: '[A-z0-9_\.]+'. We can use the second part as it is. And, the third part is going to be alphabets with '.', the pattern would be '[a-z\.]+'. Now, if we combine the three parts, we get the following pattern:
So, What's New with Replace in Oracle Analytics Data Preparation?
Let's see what's new in Replace functionality. For the demo purpose, I have created a project with a data set called Customer Profile, which has customer details like JOB_LEVEL, Email address, Phone number, Occupation, Education etc.
Earlier, in Data Preparation, there was just plain search and replace feature in the context menu of the columns. There was no option to specify a partial or entire match. Now, there are three options available in the Replace page as radio buttons.
a. Match partial values
b. Match entire values only
c. Use regular expression
When option a is selected, matches are found in both partial and entire values of the text. Let's see how 'Match partial values' work in the column Occupation. In the Prepare tab of the project, right-click on the column Occupation and select Replace.
You can see the following values in Occupation:
Enter 'Manual' in String to replace and 'Worker' in New String. Select 'Match partial values'. You will notice that both 'Manual' and 'Skilled Manual' are modified to 'Worker' and 'Skilled Worker' respectively.
Now, select 'Match entire values only' with same input. You will notice that only 'Manual' is modified to 'Worker' and 'Skilled Manual' remains the same. This option comes in handy when the requirement is specific to replace the entire values alone.
We will explore option c, 'Use regular expression' with rest of the examples.
Use of Alternation or '|'
In the column 'ENGLISH_EDUCATION' let's remove the words 'Partial' and 'Degree' from the values.
Enter 'Partial|Degree' in String to replace, leave New String as blank and select 'Use regular expression'. Now you will see that 'Partial' and 'Degree' are removed from the values like 'Partial High School', 'Partial College' and 'Graduate Degree' correspondingly.
You can use the '|' symbol as the 'OR' operator to include more than one value to match. As you might have noticed, this eliminates multiple steps when more than one matches need to be replaced with a single value.
Obfuscation, Using Literal Matches
Obfuscation is a significant step in Data Preparation, as it improves the privacy and security of sensitive content. Popular candidates for obfuscation could be Social security numbers, phone numbers, account numbers etc. With powerful features of regular expressions, it becomes easy to obfuscate in Oracle Analytics. Let's see how to do that with an example. In our data set, let's obfuscate the column Phone. If you notice the values of this column, it is divided into three parts. The first three digits within parenthesis, next three digits separated with a dot (.) from the last four.
Let's replace the digits with 'x' mark. Enter '\d+' in String to replace and enter 'xxx' in New String ('[0-9]+' and '\d+' can be used interchangeably). Select Use regular expression and you will see all the numbers are replaced with 'xxx'. Various patterns can be used according to the requirement.
Extraction with Back-Reference
Let's see how to extract specific part of the text using regular expression. Consider the column Email_address from our data set. Let's extract the email handle from the email address.
As you might recall, the pattern we constructed in the Regex example for the email address – ([A-z0-9_\.]+)@([a-z\.]+) can be divided into three parts. We're interested in the first part, which is the email handle. Enter the pattern '([A-z0-9_\.]+)@([a-z\.]+)' in String to replace and enter $1 in the New String and select Use regular expression. Now, you will just see the first part of the email address in the column. You may rename the column name as email_handle.
So, once we group and divide a text according to our need, we can use them in the output as back-references. This might be helpful where the data has multiple columns combined into one. For example, IL-Chicago, where State and City combined into one column. Columns with log entries, timestamps etc. could be interesting candidates for extraction using back-references.
Enriching Data with Regular Expressions
When we want to enrich the existing data in a highly customized format, we can use grouping and back-references together to make a powerful combination to transform data in one shot. Let's enrich the column JOB_LEVEL by inserting some text in between.
Similar to the example we saw in Grouping Patterns, JOB_LEVEL has two parts – alphabets first and digits next. The pattern '([A-Z]+)([0-9]+)' can be used to match and split into two parts. Let's insert the text '_000' between two parts. Enter '([A-Z]+)([0-9]+)' in String to replace, '$1_000$2' in New String and select Use regular expression. You will see the JOB_LEVEL is transformed into an enriched column. This could be helpful when the data is expected to be in a specific format.
What we have seen here in this blog is, just the tip of the ice-berg. A lot more can be done with Regular Expression Replace and it can be a powerful combination in Oracle Analytics Data Preparation.