I have a table which contains a list of 13 digit numbers.
I want to use informatica to break these numbers down and separate them based on cases.
For example, I have the number 1196804120316. For the first case, I wish to only take the two digits after the 68. In our example, I extract the number 04 and store it in a column.
The SQL Code for it is:
CASE WHEN ODS_CI_RPT.ADMIN.REGEXP_LIKE(DEC_REGISTRN_NBR,'^(19|20)?[0-9]{2}-[0-9]{2}-[0-9]{5,6}$')
THEN
ODS_CI_RPT.ADMIN.REGEXP_REPLACE(DEC_REGISTRN_NBR,'.*-([0-9]{2})-.*','\1',1,1)
ELSE '05'
END
AS
STATE_CODE
The next case is to take the number after 19 and store it. In this case the 68. The SQL is:
CASE WHEN ODS_CI_RPT.ADMIN.REGEXP_LIKE(DEC_REGISTRN_NBR,'^(19|20)?[0-9]{2}-[0-9]{2}-[0-9]{5,6}$') THEN
ODS_CI_RPT.ADMIN.REGEXP_REPLACE(DEC_REGISTRN_NBR,'^([0-9]{2,4})-.*','\1',1,1)
ELSE ODS_CI_RPT.ADMIN.REGEXP_REPLACE(DEC_REGISTRN_NBR,'^([0-9]{4})-.*','\1',1,1)
END
AS
D_BIRTH_YEAR,
How would I implement this using decode in informatica?
My solution to this was to use SUBSTR() in an expression. After importing the source from the table, I used:
SUBSTR(COLUMN_NAME,6,2)
To tell Informatica which position of the string I wanted broken down. Once it was broken down, the expression would capture it into a variable.