We have some old tables which has Unicode data(Japanese, Chinese, Korean etc..) but with table defination as Latin for all columns, Which shows in BO report as Junk/ Unreadable character. Now we are trying to add new columns with character set as Unicode using alter statements. The approach we are following is
1. Add new UNICODE column using alter statments
ALTER TABLE <table name> ADD <existing col name>_UNC VARCHAR(50) CHARACTER SET UNICODE;
2. Copy data from existing column to new column with translate function
UPDATE <table name> SET <col name>_unc=TRANSLATE(CAST(<col name> AS VARCHAR(50)) USING LATIN_TO_UNICODE);
But this approach doesn't solve our issue.
Please suggest how best this issue can be handled in Teradata.
Can you explain how you managed to store the unicode data in the latin columns?
Did you check the WITH ERROR option of the translate function?
Are you certain the data you want was actually stored in the LATIN column? It may have been translated to error replacement '1A'xc on loading. Verify what is actually stored using CHARTOHEXINT() function.