20 Dec 2013
4 mins read
We are having several functions in Informatica, and each function will be used depends upon the requirements. One of the most useful and less used functions is Regular Expressions. That is, ‘REG_’ functions. We can find these functions under Data Cleansing in Functions. There are 3 regular expressions available in Informatica: REG_EXTRACT, REG_MATCH and REG_REPLACE. All these regular expressions are pattern based functions rather than format based (example, date format) or data based. Means that, when the records are dynamic we cannot check the same char or string to all the incoming records. Those records can be handled by Regular Expressions.
REG_MATCH This function will simply return TRUE if the data matches the pattern else FALSE.
Syntax
REG_MATCH (subject, pattern)
Scenario-1:
One of the client requirements is to filter out the records based upon the particular pattern
‘MM-YYYY’ from source files. Since the source files are having the manually entered data, the records are not consistent. If a normal scenario like, Sal >10000 or empid between 1000 and 5000 means, we can use the conditions directly in the filter transformation and filter out the records. But when the source records are dynamic and to use some specific pattern to filter out the records, we don’t have many options here. For the above requirement we have used REG_MATCH function.
REG_MATCH (Value,‘(\w+\d\d-\d\d\d\d)’)
\w+ à is any value that follows an alphanumeric character.
\d à matches one instance of any digit from 0-9. The records whichever is not meeting the above patternwill be filtered out.Please find the below screenshots, for the source and target records details.
Source
Target
REG_REPLACE It replaces characters in a string with another character pattern. By default, REG_REPLACE searches the input string for the character pattern you specify and replaces all occurrences with the replacement pattern. You can also indicate the number of occurrences of the pattern you want to replace in the string.
Syntax :
REG_REPLACE (subject, pattern, replace, numReplacements) Scenario-2: One of the client requirements is to normalize the data by replacing the incoming data from inconsistent date format to normalized date format based upon the first occurrence. If the requirement is to replace a particular char or string, generally we will use Repalcechr or Replacestr functions. But, Repalcechr or Replacestr functions will replace all the occurrences of the particular char or string. Since the requirement is to replace a particular pattern with the first occurrence, we have used REG_REPLACE function to address the above requirement. o-by � fn��?� normal’>Scenario-2: One of the client requirements is to normalize the data by replacing the incoming data from inconsistent date format to normalized date format based upon the first occurrence. If the requirement is to replace a particular char or string, generally we will use Repalcechr or Replacestr functions. o-by � fn��?� normal’>Scenario-2: One of the client requirements is to normalize the data by replacing the incoming data from inconsistent date format to normalized date format based upon the first occurrence. If the requirement is to replace a particular char or string, generally we will use Repalcechr or Replacestr functions. But, Repalcechr or Replacestr functions will replace all the occurrences of the particular char or string. Since the requirement is to replace a particular pattern with the first occurrence, we have used REG_REPLACE function to address the above requirement. REG_REPLACE (Value, ‘/’,’-’, 1) 1 à Specifies the number of occurrences you want to replace. If you omit this option, REG_REPLACE will replace all occurrences of the character string.
Target Here the first occurrence of ‘/’ only has been replaced by ‘-‘. The remaining occurrences were not replaced with ‘-‘. The similar kind of implementation is not possible through Repalcechr or Replacestr functions. REG_EXTRACT Extracts sub patterns of a regular expression within an input value. Syntax REG_EXTRACT (subject, ‘pattern’, subPatternNum, match_from_start) Scenario-3: One of the client requirements is to extract the date in each record and to store the value in the target. Since the requirement is to extract the particular string based upon the pattern ‘MM-YYYY’, we can’t use the substr () function here. Because, we can’t determine the exact start and end positions of the substring. So, we have used REG_EXTRACT function to address the requirement. Both subPatternNum, match_from_start mentioned in the syntax are optional. REG_EXTRACT (Value,’ (\w+) (\d\d-\d\d\d\d)’, 2) \w+ à is any value that follows an alphanumeric character. \d à matches one instance of any digit from 0-9. 2 à Sub pattern number of the regular expression you want to match. Use the following Guidelines to determine the subpattern number:
Default is 1.
About the Author
BI & Analytics
11 Jun 2020
28 May 2020
08 May 2020
24 Apr 2020
13 Apr 2020
06 Apr 2020
31 Mar 2020
26 Mar 2020
23 Jun 2017
06 Aug 2015
13 Jul 2015
28 Oct 2014
17 Apr 2014
24 Mar 2014
22 Jan 2014
01 Nov 2013
26 Sep 2013
03 Sep 2013
26 Aug 2013
29 Apr 2013
04 Mar 2013
21 Feb 2013
04 Feb 2013
03 Jan 2013
17 Feb 2012
Digital Assurance
06 Feb 2013
Digital Assurance, Enterprise Solutions
14 Feb 2013
18 Feb 2013
27 Feb 2013
Others
01 Mar 2013
Enterprise Solutions
05 Mar 2013
18 Mar 2013
Digital Assurance, Enterprise Solutions, Others
22 Mar 2013
12 Apr 2013
26 Apr 2013
13 May 2013
11 Jun 2013
17 Jun 2013
25 Jun 2013
19 Aug 2013
27 Aug 2013
10 Sep 2013
19 Sep 2013
24 Sep 2013
30 Sep 2013
01 Oct 2013
03 Oct 2013
19 Nov 2013
Enterprise Solutions, Manufacturing and Consumer
28 Nov 2013
03 Dec 2013
03 Jan 2014
27 Jan 2014
31 Jan 2014
12 Feb 2014
13 Feb 2014
20 Mar 2014
11 Jun 2014
Manufacturing and Consumer
26 Jun 2014
30 Jun 2014
10 Jul 2014
15 Jul 2014
16 Jul 2014
18 Jul 2014
26 Aug 2015
28 Sep 2015
07 Oct 2015
26 Oct 2015
Infrastructure Mgmt. Services
07 Mar 2016
22 Mar 2016
13 May 2016
23 May 2016
Application Transformation Mgmt.
11 Jul 2016
25 Aug 2016
03 Sep 2016
14 Sep 2016
15 Nov 2016
22 Nov 2016
25 Nov 2016
Business Process Services
25 Apr 2017
Banking and Financial Services
18 May 2017
30 May 2017
27 Jun 2017
18 Jul 2017
26 Oct 2017
Healthcare, Insurance
28 Nov 2017
11 Dec 2017
25 Jan 2018
21 Feb 2018
14 Mar 2018
22 Mar 2018
23 Apr 2018
15 Jun 2018
18 Jun 2018
( Mandatory field * )
The information you provide will be used in accordance with our terms ofPrivacy Policy
Please Check on "I Agree" to register for the blog.