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

Question

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.

Answer 1

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.