Informatica Numeric - AlphaNumeric check

Hi

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

T1245
X58924
23783
1245

By just using TO_DECIMAL transformation  we get following in our target table:


0
0
23783
1245


So  in order to fix this issue following code can helps.


IIF
(
      REG_MATCH(COL1 , '\d+')=1  ,
      TO_DECIMAL(COL1 ),
      TO_DECIMAL( SUBSTR(COL1 ,2) )
)

Result:
1245
58924
23783
1245