Scenario:Count of vowels from string fields

Problem Statement :

Let's consider, We have employee table and we need to find out number of vowels from first_name column. So before proceeding to actual scenario. Let me explain function related to string source fields.

1. REPLACECHR() :

Definition: Replaces characters in a string with a single character or no character.

REPLACECHR searches the input string for the characters you specify and replaces all

occurrences of all characters with the new character you specify.

Syntax

REPLACECHR( CaseFlag, InputString, OldCharSet, NewChar )

Lets understand each terms in details :

a. CaseFlag : It is required argument. It must be an integer. Determines whether the

arguments in this function are case sensitive.

When CaseFlag is a number other than 0, the function is case sensitive.

When CaseFlag is a null value or 0, the function is not case sensitive.

b. InputString : It is again required argument. Must be a character string. Passes the string you want to search. If you pass a numeric value,the function converts it to a

character string. If InputString is NULL, REPLACECHR returns NULL.

c. OldCharSet : Must be a character string. The characters you want to replace. You can enter one or more characters. You can also enter a text literal enclosed within

single quotation marks, for example, 'abc'.If you pass a numeric value, the function

converts it to a character string.

If OldCharSet is NULL or empty, REPLACECHR returns InputString.

d. NewChar : Must be a character string. You can enter one character, an empty string,

or NULL.If NewChar is NULL or empty, REPLACECHR removes all occurrences of all

characters in OldCharSet in InputString.If NewChar contains more than one character,

REPLACECHR uses the first character to replace OldCharSet.

Now lets understand it with simple example,

Lets consider you have "Jason" in first_name field and you want to convert

Vowels(a,e,i,o,u) with NULL value. Then lets see first how your function looks like with

all values.

REPLACECHR(0,lower(FIRST_NAME),'aeiou',NULL)

So, if we use this function then what would be the outcome ?

Source : Jason

Output : Jsn

Now lets see configuration in expression transformation in Informatica.

V_Count :- REPLACECHR(0,lower(FIRST_NAME),'aeiou',NULL)

O_vowel_count :- length(FIRST_NAME) - length(V_Count)

I hope you understood how to use REPLACECHR() function in Informatica.