UNICODE to CHAR Translate - TRANSLATE doesnt work.

Database

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
N/A

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

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??

N/A

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).

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

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

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.

N/A

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

N/A

Re: UNICODE to CHAR Translate - TRANSLATE doesnt work.

Hi Dave,

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