In this entry i will explain how to deal with AlphaNumeric characters.
Assume that we have a target column called TRG1 which is a number field.
From source table lets say we have to a map column COL1 which contain Alphanumeric characters.
By only doing TO_DECIMAL operation only cause informatica set Alphanumeric data to 0 such as :
Lets say we have following source data
By just using TO_DECIMAL transformation we get following in our target table:
So in order to fix this issue following code can helps.
REG_MATCH(COL1 , '\d+')=1 ,
TO_DECIMAL( SUBSTR(COL1 ,2) )