top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to extract number from a string in Informatica?

+5 votes
4,816 views

I want to extract the number(0-9) from the string. e.g. if src is *Ax456*&56 then dst should be 45656.

posted Feb 5, 2014 by Dewang Chaudhary

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

2 Answers

+1 vote

Try something like this

REPLACECHR(0, REG_REPLACE(COLUMN_NAME, '[^0-9]', '/'), '/', NULL) 
answer Feb 5, 2014 by Majula Joshi
thanks it is working , i am able to get only number in string ...
0 votes

replace all other characters with an empty string. Below syntax will help you -

REG_REPLACE(COLUMN_NAME, '[^0-9]', '')
answer Apr 25, 2014 by Shatark Bajpai
Similar Questions
+1 vote

I want to extract the number(0-9) from the string. e.g.
1.if src is *J$456*&56 then tgt should be 45656
2.if src is &*(>123>>789&^ then tgt should be 123789

This is just an example, special characters or alphabets are random in string, so how to extract only numbers?

+1 vote

How can i truncate a string in informatica based of size instead of length. My problem is i had to populate a VARCHAR2(4000) field but there are some special character in the data which have size of 2 byte.

So if there are 4000 character in a string and 10 are special character with 2 byte , then overall size becomes 4020 byte which is out of range of VARCHAR2. I had tried using VARCHAR2(4000 char) but even this is not working. I don't want to use CLOB.

What i am trying to achieve is that the string should have only 4000 BYTE of data. i.e in case of above example string should have only 3990 character and 4000 BYTE.

+1 vote

I have developed an Informatica PowerDesigner 9.1 ETL Job which uses lookup and an update transform to detect if the target table has the the incoming rows from the source or not. I have set for the Update transform a condition

IIF(ISNULL(target_table_surrogate_id), DD_INSERT, DD_REJECT)

Now, when the incoming row is already in the target table, the row is rejected. Informatica writes these rejected rows into a .bad file. How to prevent this? Is there a way to determine that the rejected rows are not written into a .bad file? Or should I use e.g. a router insted of an update transform to determine if the row is insert row an then discard the other rows?

+1 vote

How to transform rows into column using Normalizer in Informatica?

+2 votes

How can we update a record in target table without using Update strategy?

...