UNICODE to CHAR Translate - TRANSLATE doesnt work.

Database
Enthusiast

UNICODE to CHAR Translate - TRANSLATE doesnt work.

Hello,

 I need to insert data into target and the source table for which has the corresp column (SRC_COL1) defined as as "varchar(50) character set unicode" The target table has the col (TGT_COL1) defined as VARCHAR(50). Also I need to look back the existing values in target so my joining columns are SRC_COL1 & TGT_COL1. The below join works fine:

SRC_COL1 = TRANSLATE(TGT_COL1 USING LATIN_TO_UNICODE)

But it fails for

TRANSLATE(SRC_COL1 USING UNICODE_TO_LATIN) = TGT_COL1

ARe these different and if no why does it fail for the second one?

Also when I do a select on SRC_COL1 - Select TRANSLATE(SRC_COL1 USING UNICODE_TO_LATIN) - it thorws 6706 - the string contains an untranslatable character.

Is there a way to handle this?

Thanks,

Indrajit

9 REPLIES
Senior Supporter

Re: UNICODE to CHAR Translate - TRANSLATE doesnt work.

You can translate all latin chars to unicode

but you can't translate all unicode chars to latin

simply due to the reason that a latin char is stored in 1 byte and a unicode in 2 to 3 bytes.

So SRC_COL1 = TRANSLATE(TGT_COL1 USING LATIN_TO_UNICODE) should work always

Enthusiast

Re: UNICODE to CHAR Translate - TRANSLATE doesnt work.

Thanks for the reply.

So is it mandatory to have the target table column defined as UNICODE ... no other alternative such as UDF or anything??

Senior Supporter

Re: UNICODE to CHAR Translate - TRANSLATE doesnt work.

If you don't want to lose any information - yes.

If you consider the non latin characters in the unicode field as garbage you could use the "WITH ERROR" option in the translate function.

From the manual:


WITH ERROR that the translation replaces offending characters in the string with a


designated error character, instead of reporting an error.


For details, see “Error Characters Assigned by the WITH ERROR


Option” on page 542).

Enthusiast

Re: UNICODE to CHAR Translate - TRANSLATE doesnt work.

Thanks a ton .. that made the trick.. we had only one record that was messing up and we could find it using ERROR option

Enthusiast

Re: UNICODE to CHAR Translate - TRANSLATE doesnt work.

Hi Ulrich,

I have a table with Unicode column and i wanted to test if we can join latin and unicode columns together. I created another table with the same column name and latin column type and inserted the same data from the source table. The insert went fine.

When i see the data in unicode table, the character is - TM(trademark) and in latin it is - ? for col1. This is perfect. When i try to join the two tables on the same column as in query below, the first query doesn't give any records but the 2nd does.

1.

sel  A.* , B.*
from
 db1.tab_unicode A ,  db1.tab_latin B
WHERE A.col1 = TRANSLATE(B.col1 USING LATIN_TO_UNICODE)
--WHERE translate(A.col1 using unicode_to_latin)= B.col1

i am converting latin column to unicode and then comparing

2.

sel  A.* , B.*
from
 db1.tab_unicode A ,  db1.tab_latin B
--WHERE A.col1 = TRANSLATE(B.col1 USING LATIN_TO_UNICODE)
WHERE translate(A.col1 using unicode_to_latin)= B.col1

i am converting unicode column to latin and then comparing

1. Any idea why is this behaving so ? i read the posts above and the first one should have worked .

2.

sel   
TRANSLATE(B.col1 USING LATIN_TO_UNICODE)
from
db1.tab_latin B

Would this conversion, display TM as the output ?

--Samir

Enthusiast

Re: UNICODE to CHAR Translate - TRANSLATE doesnt work.

Hi Ulrich,

  sel  translate( col1 USING UNICODE_to_latin)
 ,col1
 from  db1.tab1

This displays TM as ? but translate( col1 USING latin_to_unicode) doesnt show ? as TM. i am suing sql assist and characterset UTF16 and .net.

Junior Contributor

Re: UNICODE to CHAR Translate - TRANSLATE doesnt work.

A should be displayed correctly regardless of Latin or Unicode.

If it's anything else you did something wrong.

SELECT CHAR2HEXINT(col1) should result in '99' (Lartin) and '0099' (Unicode)

Teradata Employee

Re: UNICODE to CHAR Translate - TRANSLATE doesnt work.

The Unicode (U+2122) TRADE MARK SIGN does not translate to Teradata Latin. 0x99 is a control character in Teradata Latin. See the international Character Set Support reference for more details.

- Dave

Junior Contributor

Re: UNICODE to CHAR Translate - TRANSLATE doesnt work.

Hi Dave,

of course you're correct. My session was set to ASCII instead of UTF.