top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to truncate a string in informatica based of size instead of length

+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.

posted Aug 24, 2013 by anonymous

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

1 Answer

0 votes

Be careful. when you truncate string with multibyte characters to byte size, you can receive invalid string. The end of string, after truncation, maybe placed between bytes, which create one character.

So my suggestion is to truncate string to number of characters(by length) and double size of the field in target database. When you truncate to x characters target field must have 2x length in bytes. Of course you can have 4000 bytes in target and truncate to 2000 characters source.

answer Oct 6, 2014 by Shweta Singh
Similar Questions
+5 votes

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

+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?