top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

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

+2 votes
2,660 views

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?

posted Mar 21, 2014 by Rohini Agarwal

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote

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))

answer Mar 21, 2014 by Shweta Singh
+1 vote

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()

answer Mar 21, 2014 by Madhavi Kumari
Similar Questions
+1 vote
How to convert string into number using expression transformation in informatica?

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.

+1 vote
Informatica Expression Convert String to Date

I need to convert a string to a date so I can do a compare with what is in my column. My statement is this:

Decode(IsNew, FALSE, 
  IIF (v_save_view_row_obsolete_day <> 
    TO_DATE('9999-12-31 00:00:00 AM','YYYY-MON-DD HH24:MI:SS'), 
     '9999-12-31 00:00:00', v_save_view_row_obsolete_day))

When I validate I get this error: enter image description here

+1 vote
Formatting a column using Expression Transformation in Informatica Powercenter

I have a Column (SALARY) in Source Table from Relational DB, for example 15000 is a record in SALARY column and I want to format it as $15,000.00 into the Target table which is a Relational DB using Expression Transformation.

Thankyou

...