top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Replacing a text in Informatica

+2 votes
306 views

I have data in informatica which I would like to do a textual search/replace. I have a column in the source where records are in "12M" format and I want to replace "M" with "10000000".

For example, "12M" would be come a numeric "120000000"

posted May 27, 2014 by Rohini Agarwal

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

1 Answer

+2 votes

Check the following code 

IIF(
  SUBSTR(Column3,-1,1)='M',
  TO_DECIMAL(SUBSTR(Column3,1,(LENGTH(Column3)-1)))*10000000,
  TO_DECIMAL(Column3)
)

As per me this is the correct way to replace the test?

answer May 28, 2014 by Shweta Singh
Similar Questions
+3 votes
Search Informatica for text in SQL override

Is there a way to search all the mappings, sessions, etc. in Informatica for a text string contained within a SQL override?

For example, suppose I know a certain stored procedure (SP_FOO) is being called somewhere in an INFA process, but I don't know where exactly. Somewhere I think there is a Post SQL on a source or target calling it. Could I search all the sessions for Post SQL containing SP_FOO ? (Similar to what I could do with grep with source code.)

+1 vote
Text file location and its data check at FTP server in informatica

How to check in informatica whether a flat file exists at a location at FTP server and to check if it has some data in it?

+1 vote
Replacing special characters by null

Is there any function to replace the special characters by null in informatica

if we used replacestr function, i think we should specify all special characters

as follows replacestr(input,'!','~','@','#','$','%','^','&','*',null)

But we dont know what are teh special characters will be coming as input.

can u please let me know that which function will be suitable.

+2 votes
Informatica: Text file location and its data check on FTP server

How to check using informatica whether a flat file exists at a location at FTP server also to check if it contains some data?

+1 vote
How to copy the data loaded into target table from source table to the text file?

I have to load the target table copy of data into the text file at the time of workflow running.(i.e)Whatever data are going into the the target table that should come into the text file also.

...