Clever Geek Handbook

How can I convert a string to a number using expression conversion in informatica?

I have an original column (sum) with a data type string, contains data like $ 793.00, $ 5791.00, ...

I need to load this same data into a column of the target table (count) with data type NUMBER

how can i get this same data with '$' character in target using expression conversion in informatica?

anyone help me, thanks in advance.

+2


source to share


4 answers


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

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



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

+3


source


You can take the original "sum" column into one expression element, for example "AMOUNT_INPUT", and add a new element to this expression so that "AMOUNT_OUTPUT" is expressed as TO_NUMBER (AMOUNT_INPUT) "



+1


source


Some versions of Informatica do not support TO_NUMBER()

. If it is for the version you are using, you will need to use one of the following values ​​appropriate for your use case:

  • TO_INTEGER()

  • TO_FLOAT()

  • TO_DECIMAL()

Please refer to the link of Informatica functions for use.

+1


source


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

0


source






More articles:


All Articles