Untranslatable characters, and translate_chk, and Unicode! Oh My!

Database
Enthusiast

Untranslatable characters, and translate_chk, and Unicode! Oh My!

I'm having an issue with an 'untranslatable character' error. I've read numerous dev forum posts on the topic, reviewed TD documentation, and still can't find a solution.

I'm trying to create a table by selecting some data from a few columns (themselves all from one table), then using OREPLACE to remove all vertical pipes ('|') from one of the columns in my new table. I need to remove the |'s, as they're used as a delimiter by software that eventually uses the output.

The column that's giving me fits is item_name. That column is in unicode, as SHOW TABLE lists that column as 'item_name VARCHAR(381) CHARACTER SET UNICODE NOT CASESPECIFIC'. The code I’m using to remove the |’s is: SELECT OREPLACE(item_name,'|','') FROM databaseB.sales;. That OREPLACE statement generates the 'The string contains an untranslatable character' error.

The column in question has characters from every language/character set imaginable: Latin, Chinese, Cyrillic, Hebrew, Japanese, etc, etc. From my readings of how character sets are used in TD, I thought that having the data stored in a Unicode would resolve any such ‘multi-character set’ issues.

I created a temp table that grabbed a large number of rows from item_name, primarily with strings in English and Russian (Latin and Cyrillic). I then ran TRANSLATE_CHK: SELECT item_name FROM databaseB.sales WHERE TRANSLATE_CHK(item_name USING UNICODE_TO_LATIN) <> 0; to see what wasn't 'translating'. That query returned all rows that contained Cyrillic characters.

What am I missing here? Why can’t TD replace the |’s?

1 REPLY
Enthusiast

Re: Untranslatable characters, and translate_chk, and Unicode! Oh My!

A Teradata guru, internal to my company, solved the problem for me: A non-Unicode-compatible version of oreplace is installed in our syslib, and a Unicode-compatible version is in our td_sysfnlib. When the database is not specified, syslib is used before td_sysfnlib. So forcing TD to use the td_sysfnlib version of oreplace solved the problem. Here's the code used: 

SELECT td_sysfnlib.OREPLACE(item_name,'|','') FROM databaseB.sales;

I hope that helps anyone else who's running into the same issue!