Using Common Informatica Expressions
When you click the "fx" icon, a "Field Expression" dialog box appears, which includes a section called "Function Description and Syntax" to help assist with writing the expressions.
Below are a few commonly used expressions in Informatica with examples showing how to write the syntax to translate the data. For a full listing, please reference the "Functions" list in the "Field Expression" dialog box.
|
Expression |
Description |
Syntax |
Example |
|---|---|---|---|
| CHR | CHR returns the ASCII character corresponding to the numeric value you pass to this function. ASCII values fall in the range 0 to 255. You can pass any integer to CHR, but only ASCII codes 32 to 126 are printable characters. | CHR ( numeric_value ) |
'1001 E Hector St' || CHR(13) || 'Suite 100' will integrate as:
Suite 100 |
| INITCAP | INITCAP capitalizes the first letter in each word of a string and converts all other letters to lowercase. Words are delimited by white space (a blank space, formfeed, newline, carriage return, tab, or vertical tab) and characters that are not alphanumeric. For example, if you pass the string ‘…THOMAS’, the function returns Thomas | INITCAP ( string ) |
INITCAP('thomas') or INITCAP('THOMAS') integrates as the value "Thomas" |
| LENGTH | Returns the number of characters in a string, including trailing blanks. | LENGTH ( string ) | LENGTH('TargetX') = 7 |
| LOWER | Converts uppercase string characters to lowercase. | LOWER ( string ) | LOWER('THOMAS') = thomas |
| LPAD | Adds a set of blanks or characters to the beginning of a string to set the string to a specified length. | LPAD (first_string, length, [second_string] ) | LPAD('5224', 6, '0') = 005224 |
| REPLACECHR | 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. | REPLACECHR ( Flag, InputString, OldCharSet, NewChar ) |
REPLACECHR(0, 'Some-hyphenated-data', '-', ' ') = Some hyphenated data Note: If Flag is set to true, the function is case sensitive. |
| SUBSTR | Returns a portion of a string. SUBSTR counts all characters, including blanks, starting at the beginning of the string. | SUBSTR (string, start, [length] ) | SUBSTR('ABCDEFG', 0, 4) = ABCD |
| UPPER | Converts lowercase string characters to uppercase. | UPPER ( string ) | UPPER('acme') = ACME |
| TO_DATE |
Converts a character string to a date datatype in the same format as the character string. You use the TO_DATE format strings to specify the format of the source strings. Note: The target column must be date/time for TO_DATE expressions. |
TO_DATE ( string, [format] ) | TO_DATE('12/2/2010', 'MM/DD/YYYY') = 12/02/2010 TO_DATE('12/2/10', 'MM/DD/RR') = 12/02/2010 |
| ISNULL |
Returns whether a value is NULL. ISNULL evaluates an empty string as FALSE. Note: To test for empty srings, use LENGTH. |
ISNULL ( value ) | ISNULL('Is It?') = FALSE ISNULL(NULL) = TRUE |
| GET_DATE_PART | Returns the specified part of a date as an integer value. For example, if you create an expression that returns the month portion of the date, and pass a date such as Apr 1 1997 00:00:00, GET_DATE_PART returns 4. | GET_DATE_PART ( date, format ) | GET_DATE_PART('12/2/2010', 'MM') = 12 |
| DECODE |
Searches a column for a value that you specify. If the function finds the value, it returns a result value, which you define. If no matching value is found, the default value is returned. You can build an unlimited number of searches within a DECODE function. Note: If you use DECODE to search for a value in a string column, you can either trim trailing blanks with the RTRIM function or include the blanks in the search string. |
DECODE( value, first_search, first_result, [ second_search, second_result]...[,default] ) |
DECODE('Male', DECODE('M', |
| IIF | Returns one of two values you specify, based on the results of a condition. | IIF( condition, value1, value 2 ) | IIF('CRM' = 'CRM', 'They Match!', 'No match') = They Match! IIF(15 > 50, 'Win', 'Lose') = Lose |