Starting with Oracle database 10gR1, 4 Regular Expression functions were introduced that you can use in SQL and PL/SQL statements. This post will illustrate how to use the REGEXP_REPLACE function in a flat file to Oracle database mapping using ODI. The beauty of the ODI implementation is that it takes advantage of the technology within the target database engine to perform the transformations and other functions against mapped data. Because of this, REGEXP functions implemented within the database versions are available in these mappings. So let’s look at the following example taken from my OOW14 presentation:

My data capture system requires phone numbers to be entered using a ###.###.#### (404.555.5555) construct, However, when reporting phone numbers, the user wants to see 1 (###)-###-####. The following query will do this for us:

SELECT REGEXP_REPLACE (phoneNumber, ‘([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})’, ‘1 (\1)-\2-\3’)
FROM customers

Operating Environment:
Oracle Linux 6.5
Database 11.2.0.4
ODI 12.1.3

Before getting into ODI, here is a look at the Target and Source setup

Target

 

 

 

 

Source

 

 

 

 

 

 

Now let’s build this same statement in a ODI mapping.

• Create the logical mapping. In this case, it is a direct mapping between the source (flat-file) and the target (Oracle database)
• Under the Target tab, enter the REGEXP function statement
  • REGEXP_REPLACE (column_name, ‘([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})’, ‘1 (\1)-\2-\3’)
• Make sure Execute on Hint is set to Target

•  Switch over to the Physical view and click Run

 

So you can see the where the REGEX Function gets processed, here is the simulated run of the Mapping:

Once the process is complete, we can query the target Oracle database table an see that the phone numbers do indeed meet the user requirements.

That’s What’s On Tap Today