Informatica Expression Transformation - Function Table
Function Syntax Purpose How to Use
IIF IIF(condition, true_value, false_value) Conditional logic (if-else) IIF(SALARY > 10000, 'HIGH', 'LOW')
DECODE DECODE(expr, val1, res1, val2, res2, ..., Compares an expression DECODE(GRADE, 'A', 90, 'B', 80, 0)
default) to multiple values
ISNULL ISNULL(expr) Checks if a value is NULL IIF(ISNULL(COMM), 0, COMM)
NVL NVL(expr1, expr2) Replaces NULL with a NVL(COMM, 0)
default value
NULLIF NULLIF(expr1, expr2) Returns NULL if expr1 = NULLIF(SALARY, 0)
expr2
LKP LKP:lookup_name(column_name) Perform a lookup in LKP:EMP_LOOKUP(DEPTNO)
Expression (rare)
TO_CHAR TO_CHAR(date [, format]) Converts date to string TO_CHAR(SYSDATE, 'YYYY-MM-DD')
TO_DATE TO_DATE(string [, format]) Converts string to date TO_DATE('2024-12-01', 'YYYY-MM-DD')
TO_INTEGER TO_INTEGER(expr) Converts to integer TO_INTEGER(SALARY)
TO_FLOAT TO_FLOAT(expr) Converts to float TO_FLOAT(PRICE)
SUBSTR SUBSTR(string, start [, length]) Extracts a substring SUBSTR(ENAME, 1, 3)
INSTR INSTR(string, search_string) Finds position of a INSTR(EMAIL, '@')
substring
LENGTH LENGTH(string) Returns length of a string LENGTH(ENAME)
UPPER UPPER(string) Converts to uppercase UPPER(ENAME)
LOWER LOWER(string) Converts to lowercase LOWER(ENAME)
INITCAP INITCAP(string) Capitalizes each word INITCAP(ENAME)
LTRIM / RTRIM / LTRIM(str) / RTRIM(str) / TRIM(str) Removes spaces TRIM(ENAME)
TRIM
SYSDATE SYSDATE Current system date SYSDATE
ROUND ROUND(number [, decimal_places]) Rounds a number ROUND(SALARY, 2)
TRUNC TRUNC(number [, decimal_places]) Truncates a number TRUNC(SALARY, 2)
MOD MOD(number1, number2) Returns remainder MOD(ID, 2)
POWER POWER(base, exponent) Raises to power POWER(2, 3)
ABS ABS(number) Absolute value ABS(-100)
CEIL / FLOOR CEIL(number) / FLOOR(number) Rounds up/down CEIL(4.2), FLOOR(4.8)
CONCAT CONCAT(str1, str2) Joins two strings CONCAT(FNAME, LNAME)
CHR CHR(integer) Converts ASCII code to CHR(65)
character
ASCII ASCII(character) Returns ASCII code of ASCII('A')
character
LPAD / RPAD LPAD(str, len, pad_chr) / RPAD(...) Pads string LPAD(ID, 5, '0')
REPLACE REPLACE(str, search, replace) Replaces part of a string REPLACE(NAME, 'a', '@')
