EIM Data Cleansing : Oracle function – Regular Expression

When loading data into Siebel through EIM, we do perform Data cleansing/massaging as per our

business requirement. The cleansed data from Source is then loaded into the target EIM

Interface tables

Scenario:

Convert multiple spaces in address related columns to a single space for all records

dynamically. Please refer the example cited below,

Oracle built in functions like replace , decode and translate was not yielding the desired

result due to search complexity. Writing a custom procedure / function to do this address

cleansing will be tedious and complex process, as it has to cleanse all records dynamically.

Implementation :

We have an OOB solution for this by using the regular expression (REGEXP_REPLACE) function

provided by Oracle from 10g version which improves the ability to search and manipulate

character data.

Syntax :

SELECT ADDR AS SOURCE,REGEXP_REPLACE(ADDR,'( ){2,}', ' ') AS TARGET

FROM SIEBEL.S_ADDR_PER WHERE PER_ID IN ('1-23O0-1625','1-G8FU-133')

You are here