CODEWITHSUNDEEP
replacecharacterinformatica
neo
neo

Reputation: 1

Remove or replace '�' character in Informatica

We have a requirement wherein we need to replace or remove '�' character (which is an unrecognizable, undefined character) present in our source. While running my workflow it runs successfully but when i check the records in target they are not committed. I get the following error in Informatica

Error executing query for record 37: 6706: The string contains an untranslatable character.

I tried functions like replace_chr, reg_replace, replace_str etc., but none seems to be working. Kindly advise on how to get rid of this. Any reply is greatly appreciated.

Upvotes: 0

Views: 9843

Answers (4)

Daniel Machet
Daniel Machet

Reputation: 630

Special characters are a challenge and having checked the informatica network I can see there is a kludge involving replace_str setting first a variable to the string with all non special characters first and then using the resulting variable in a replace_str so that the final value has only the allowed characters https://network.informatica.com/thread/20642 (awesome workaround by nico so long as you can positively identify every character that should be allowed) ...

As an alternate kludge I would also attempt something using an xml transformation somewhere within the mapping as informatica conveniently converts special characters to encoded (decimal or hex I cant remember) values... so long as you can live with these encoded values appearing in your target text you should be fine ( and build some extra space into your strings to accommodate any bloatage from the extra characters

Upvotes: 0

Maciejg
Maciejg

Reputation: 3353

You need to fetch rows with the appropriate character set defined on your connection. What is the connection you're using, ODBC or native? What's the DB?

Upvotes: 0

Jim Macaulay
Jim Macaulay

Reputation: 5165


You can replace the special characters in an expression transformation.

REPLACESTR(1,Column_Name,'?',NULL)

REPLACESTR - Function
1 - Position
Column_Name - Column name which has a special character
? - Special character
NULL - Replacing character

Upvotes: 0

xav56883728
xav56883728

Reputation: 315

You need to use in your schema definitions charset=> utf8-unidode-ci

but now you can do:

UPDATE tablename
SET columnToCheck = REPLACE(CONVERT(columnToCheck USING ascii), '?', '')
WHERE ...

or

update tablename
set columnToCheck = replace(columnToCheck , char(146), '');

Replace NonASCII Characters in MYSQL

Upvotes: 0

Related Questions