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.
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.