A regular expression (regex or regexp for short) is a special text string for describing a search pattern. Each time you use Regular Expression, you invoke a powerful search engine.You can think of regular expressions as wildcards on steroids.For example,
the regular expression can be used to search for an email address.
Other uses of Regular Expressions:
- It can match one string to another
- It can be used to match upper and lower case
- Regular Expression can quantify how many times a character repeats
- Regular Expression can match whole classes of characters
- Regular Expression allows you to match any character by using a period
- The regular expressions can be combined by placing these expressions side-by-side like:-
Match any character at the beginning of a line followed by a capital Z
In this article, I will show you different ways of using regular expressions in xFusion.
I will first show you how to use a regular expression to identify cheque numbers that are not numeric.
In a regular expression, a number is represented as :^[0-9]+$
xFusion supports a function called RegexFind that helps us identify the given input matches a specific pattern. We use this function, along with "Filter" function, to identify all the records where cheque number is invalid.
The examples are based on our data cleansing project at Tata.
Example 1: Cheque Number Validation
Step 1: Create Connection to Source Data
Connect to the Source Data from xFusion and Create a Standard Query to extract the source data. Click on Preview Button to see table containing the data.
Step 2: Apply Split Function to get the Desired Data
Here we apply business logic to our Source Data.
We will extract all the Integer valued Cheque Nos. with specific pattern. Create a Transform Query
and use "Split" to split the String into two separate strings.
Step 3: Apply RegexFind to find all the integer valued Cheque Nos.
With the help of "Filter" and "RegexFind" function we check all the valid integer records.
Example 2: Regular Expressions to parse the group of input data.
Step 1: First create a connection to your data using a connection
Step 2: Create a Transform Query to apply RegexParse to extract all date values into separate columns.
Now we will parse the input date columns to split all values into separate columns using RegexParse.
|