ETL新手咨询Informatica PowerCenter 9.*：过滤空姓名与清洗联系方式

Hey there! As someone who’s spent plenty of time working with Informatica PowerCenter 9.*, let’s break down your two questions clearly so you can get your ETL job up and running smoothly.

1. Removing Records Where Both First Name and Last Name Are Empty #

Go with the Filter Transformation—this is exactly what it’s built for: filtering out records that don’t meet your criteria. Here’s why the other options aren’t the best fit:

• Expression Transformation: It’s great for calculating new fields or modifying existing ones, but it can’t directly exclude entire records from flowing to the target.
• When you mention "Informatica built-in features," the Filter transformation is the built-in tool tailored for this exact use case.

To set up the Filter, paste this condition into the Filter Condition field:

NOT ( (ISNULL(First_name) OR First_name = '') AND (ISNULL(Last_name) OR Last_name = '') )

This will keep only records where at least one of First name or Last name has a value, and drop any entries where both fields are empty.

2. Cleaning the Contact No Column #

For this task, you’ll need an Expression Transformation to modify the Contact No field. The most efficient way to strip out all non-numeric characters (like +, (, ), -, and spaces) is using the REG_REPLACE function, which is supported in PowerCenter 9.*.

Create a new field (let’s name it Clean_Contact_No) in the Expression transformation with this logic:

REG_REPLACE(Contact_No, '[^0-9]', '')

• The regex [^0-9] matches any character that isn’t a digit.
• We replace those matches with an empty string, leaving only the numeric digits behind (exactly like your example: +1(123) 456-789 becomes 123456789).

If for some reason your environment doesn’t support regex functions (unlikely in 9.*), you can nest REPLACE functions as a fallback:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Contact_No, '+', ''), '(', ''), ')', ''), '-', ''), ' ', '')

This targets each unwanted character individually—it’s less elegant, but it gets the job done.

内容的提问来源于stack exchange，提问作者Mangesh Sathe