How to convert string into number using expression transformation in informatica?

2
votes

I have source column(amount) with datatype string, contains the datas like $793.00, $5791.00,...

I need to load this same data into the target table column(amount) with datatype NUMBER

how can i get this same data with '$' symbol in target by using expression transformation in informatica?

anyone help me please, thanks in advance.

4
informatica
@madhina, do you mean "with" or "without" the $ symbol ? If you wish to convert this to a number, you'll have to strip out the '$' symbol. - Rahul

4 Answers

3
votes

TO_NUMBER(SUBSTR(AMOUNT,INSTR(AMOUNT,'$')+1,LENGTH(AMOUNT)-1))

or if it's always the first character and you don't have to worry about spaces

TO_NUMBER(SUBSTR(AMOUNT,2,LENGTH(AMOUNT)-1))

1
votes

You can take source column "amount" into one expression element, say "AMOUNT_INPUT" and add a new item in that expression such that "AMOUNT_OUTPUT" in this make a expression as "TO_NUMBER(AMOUNT_INPUT)"

1
votes

Some versions of Informatica do not support TO_NUMBER(). If that is the case with the version you are using, you will need to use one of the following, as appropriate for your use case:

  • TO_INTEGER()
  • TO_FLOAT()
  • TO_DECIMAL()

See the reference of Informatica functions for usage details.

0
votes

You can also use below logic to get the desired result - REPLACESTR(1,AMOUNT,'$','')