Informatica Cloud (IICS) Functions Guide

String Functions

1. LTRIM

The LTRIM Function in Informatica Cloud removes blanks or specified characters from the beginning of a string. It removes single-byte spaces if no characters are defined to be removed using the trim_set parameter.

Syntax

LTRIM( string , [trim_set] )

The following example removes blank spaces from the beginning of a string in the Name column.

LTRIM( NAME )

The following example removes the characters ‘A’ and ‘a’ and ‘.’ from the beginning of the strings in the NAME column resulting in the modified strings shown in the RETURN_VALUE column.

LTRIM( NAME, 'Aa.' )

LTRIM is case-sensitive and searches the string character by character.

LTRIM removes ‘.aA’ from the name ‘.aAron’ even though the ‘.’ is the last character in the trim_set. This is because LTRIM searches, character-by-character, for the set of characters you specify in the trim_set argument.

2. RTRIM

The RTRIM Function in Informatica Cloud removes blanks or specified characters from the end of a string. It removes single-byte spaces if no characters are defined to be removed using the trim_set parameter.

Syntax

RTRIM( string , [trim_set] )

The following example removes blank spaces from the end of a string in the Name column.

RTRIM( NAME )

The following example removes the characters ‘A’ and ‘a’ and ‘.’ from the beginning of the strings in the NAME column resulting in the modified strings shown in the RETURN_VALUE column.

RTRIM( NAME, '.,' )

RTRIM is case-sensitive and searches the string character by character.

The LTRIM and RTRIM functions are used together as a data-cleansing mechanism to remove leading and trailing spaces from a string field.

LTRIM( RTRIM( NAME ) )

3. LPAD

The LPAD function in Informatica Cloud adds a set of characters to the beginning of the string to set the string to a specified length. It returns NULL if the value passed to the function is NULL or if the length specified is a negative number.

Syntax

LPAD( string, length, second_string)

The following expression sets the period length to two by padding them with leading zeroes.

LPAD( PERIOD, 2, '0')

4. RPAD

The RPAD function in Informatica Cloud adds a set of characters to the end of the string to set the string to a specified length. It returns NULL if the value passed to the function is NULL or if the length specified is a negative number.

Syntax

RPAD( string, length, second_string)

The following expression sets the decimal number length to six by padding them with trailing zeroes.

RPAD( AMOUNT, 6, '0')

5. CONCAT

The CONCAT function in Informatica Cloud concatenates two strings. It converts all data to text before concatenating the strings and does not add a space or separator between both strings.

Syntax

CONCAT( first_string, second_string )

The following example concatenates the FIRST_NAME and LAST_NAME fields using the CONCAT function.

CONCAT( FIRST_NAME, LAST_NAME )

To add a space between two strings using the CONCAT function, use nested CONCAT functions as shown below.

CONCAT( CONCAT( FIRST_NAME, ' ' ), LAST_NAME )

Alternatively, the double pipe || operator can be used to concatenate strings instead of the CONCAT function. It is much simpler and also improves performance.

FIRST_NAME ||' '|| LAST_NAME

6. SUBSTR

The SUBSTR function in Informatica Cloud returns a portion of a string of specified length from a specified position.

Syntax

SUBSTR( string, start_position, length )

• If no length is specified, the SUBSTR function returns the entire string from the specified position.
• When a negative value is provided, the start_position is calculated by counting the characters from right to left. However, it’s important to note that the substring extraction still occurs from left to right based on the length specified in the function.
• A negative value can also be passed as a start_position.

The following are examples of the usage of the SUBSTR function in Informatica Cloud.

7. INSTR

The INSTR function in Informatica Cloud returns the position of a character set, counting from left to right.

Syntax

INSTR( string, search_value ,start ,occurrence )

• INSTR searches from the first character in the string if no startvalue is specified.
• INSTR searches for the first occurrence of search_value if no value is specified for occurance.
• When a negative value is passed as a start position in the INSTR function, the search starts from the last character of the string. But the position is determined by counting from left to right.

The following are examples of the usage of the INSTR function in Informatica Cloud.

8. Nested INSTR

The INSTR function can be nested with other functions like SUBSTR to accomplish more complex tasks.

The following example extracts the FIRST_NAME value from the FULL_NAME field.

SUBSTR( FULL_NAME, 1, INSTR( FULL_NAME, ' ', 1, 1) - 1)

The following example extracts the LAST_NAME value from the FULL_NAME field.

SUBSTR( FULL_NAME, 1, INSTR( FULL_NAME, ' ', -1, 1) + 1)

Conditional Functions

1. IIF

The IIF function is used to perform “IF” and “ELSE” operations in Informatica Cloud. It returns one of two values you specify based on the results of a condition.

Syntax

IIF( condition, value1 [,value2] )

• condition: The condition the user wants to evaluate.
• value1: The value to be returned if the condition is TRUE.
• value2: The value to be returned if the condition is FALSE. Optional.

If the value2 is omitted in the IIF function, the following values are returned when the condition is FALSE based on the datatype of the field.

The following example returns compares the fields ‘a’ and ‘b’ and returns the highest value.

IIF( a > b, a, b )

Nested IIF

Use nested IIF statements to test multiple conditions.

The following example returns compares the fields ‘a’, ‘b’, and ‘c’ and returns the highest value.

IIF( a > b, a, IIF( b > c, b, c ) )

This expression checks if a is greater than b. If true, it returns a. If false, it checks if b is greater than c. If true, it returns b. Otherwise, it returns c.

The following example returns the bonus value of employees based on their experience.

IIF(exp > 5, salary * 0.5, 
    -- if experience is greater than 5 years
    IIF(exp > 2 and exp <= 5, salary * 0.3, 
        -- if experience is between 3 and 5 years
        salary * 0.2) 
        -- else for experience less than or equal to 2 years
)

2. DECODE

The DECODE function in Informatica Cloud evaluates a series of predefined conditions and returns a value when the first condition is met. Additionally, you have the option to specify a DEFAULT value to return when none of the predefined conditions are met. This functionality is analogous to the CASE statement in SQL.

Syntax

DECODE( value,
    first_search, first_result,
    second_search, second_result,
    default_value
)

• value is the field or a valid expression you want to search.
• first_search, second_search, etc., are the values for which you want to search.
• first_result, second_result, etc., are the values you want to return if the search finds a matching value.
• default_value is the value returned if the search does not find a matching value.

The following example checks the value of COUNTRY_CODE against the specified codes and returns the respective country names. If none of these codes match, it returns ‘NA’.

DECODE(COUNTRY_CODE,
       'USA', 'UNITED STATES OF AMERICA',
       'IND', 'INDIA',
       'AUS', 'AUSTRALIA',
       'NA')

Multiple Conditions in DECODE

The DECODE function can also test multiple conditions defined on multiple columns. The syntax of the DECODE function for validating multiple conditions is slightly different.

Syntax

DECODE( result,
   first_condition, first_return_value,
   second_condition, second_return_value,
   default_value
)

• result is the result of the condition being evaluated in boolean.
• first_condition, second_condition, etc., are the conditions to be evaluated.
• first_return_value, second_return_value, etc., are the values to be returned if their corresponding condition is true.
• default_value is the value returned if none of the specified conditions are met.

The following example checks multiple conditions to determine a country name and returns ‘NA’ if none of the conditions match.

DECODE(TRUE,
  COUNTRY_CODE = 'USA' OR COUNTRY_CODE = '+1' , 'UNITED STATES OF AMERICA',
  COUNTRY_CODE = 'IND' OR COUNTRY_CODE = '+91', 'INDIA',
  COUNTRY_CODE = 'AUS' OR COUNTRY_CODE = '+61', 'AUSTRALIA',
  'NA'
)

IIF vs DECODE

IIF is widely preferred for simple IF and ELSE conditions. Multiple conditions can also be defined using Nested IIF functions but DECODE improves readability.

The following example shows how to use IIF instead of the DECODE function using the example from the previous section.

IIF(COUNTRY_CODE = 'USA' OR COUNTRY_CODE = '+1', 'UNITED STATES OF AMERICA',

    IIF(COUNTRY_CODE = 'IND' OR COUNTRY_CODE = '+91', 'INDIA',

        IIF(COUNTRY_CODE = 'AUS' OR COUNTRY_CODE = '+61', 'AUSTRALIA', 'NA')))

3. IN

The IN function in Informatica Cloud matches input data to a list of values and returns TRUE if there is a match and returns FALSE if there is no match and returns NULL if the input value is null.

Syntax

IN( InputField, value1, value2, … , CaseFlag)

• InputField: This is the field or expression you want to check against the list of values.
• value1, value2, and so on: These are the values against which you want to compare InputField.
• CaseFlag: This optional parameter is used to control case sensitivity in comparisons. If CaseFlag is set to 1, the comparison is case-sensitive. If it’s set to 0 or omitted, the comparison is case-insensitive.

The following example filters all the employees belonging to department ID 90, 100, and 101 using the IN function.

IN ( DEPARTMENT_ID, 90, 100, 110, 0)

The same condition can be applied using OR as shown below.

DEPARTMENT_ID = 90 OR DEPARTMENT_ID = 100 OR DEPARTMENT_ID = 110

This example demonstrates how the IN function simplifies the logic by encapsulating all the conditions into a single statement, enhancing the readability and maintainability of the code.

Subscribe to our Newsletter !!

Related Articles: