Simple data conversion , string handling, testing conditions in Informatica expression transformation

Simple data conversion , string handling, conditions in Informatica expression transformation

Testing conditions in Informatica expression transformation use:

1) If condition in Informatica:

IIF(condition, true, false)

2) To test for null

ISNULL(<portname>)

When used along with IIF statement, it would look like:

IIF(ISNULL(NAME), '', NAME)

NAME is the port that you are testing to check if it is null. If null you are passing '' blank character otherwise you are sending the NAME as it is .

3) To test if the incoming port is a decimal or integer you can use the below functions:

IS_DECIMAL() or IS_INTEGER() or IsNumber()

For example:

IIF( Not IS_INTEGER(i_PRICE) OR ISNULL(i_PRICE),'Invalid Price', '')

4) To check length of a port which is of string data type :

IIF(LENGTH(i_NAME) > 0, i_NAME, '')

String Handling in Informatica expression transformation use:

1) To search for a string in a port and get the position of the string you can use INSTR Function. SUBSTR function can be used to used to
extract part of string>

substr(NAME,1,INSTR(NAME,'-',1)-1)

In the above example, everything upto - is extracted from the incoming port NAME.

2) To concat strings from multiple ports use || funcation. For example:

i_port1 || i_port2

3) REPLACESTR function can be replace characters in a string; For example, char(10), chr(13) are replaced with '' blanks in the port NAME.

IIF(isnull(NAME),' ', REPLACESTR (1, NAME,CHR(10),CHR(13), ''))

In the below example, - are removed from date ID and then converted to integer:

TO_INTEGER(REPLACECHR(FALSE, v_DATE_ID, '-', ''))

4) To remove blanks from a port you can use LTRIM or RTRIM functions: In example below, blank spaces are removed before checking if the value  of port NAME is equal to '?'.

IIF(LTRIM(RTRIM(NAME)) = '?', 'N', 'Y')

Date manipulation in Informatica expression transformation:

In the below example, port TIMESTAMP is converted to dates in YYYY-MM-DD and YYYY-MM-DD HH24:MI:SS format respectively.

TO_CHAR(trunc(TIMESTAMP), 'YYYY-MM-DD')

TO_DATE(TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')