All Forums Database
indrajit_td 50 posts Joined 10/09
15 May 2012
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

 

 

 

ulrich 816 posts Joined 09/09
15 May 2012

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

indrajit_td 50 posts Joined 10/09
16 May 2012

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

ulrich 816 posts Joined 09/09
16 May 2012

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

indrajit_td 50 posts Joined 10/09
16 May 2012

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

teradatauser2 236 posts Joined 04/12
28 Jul 2015

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

teradatauser2 236 posts Joined 04/12
28 Jul 2015

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.

dnoeth 4628 posts Joined 11/04
29 Jul 2015

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)

Dieter

david.craig 73 posts Joined 05/13
29 Jul 2015

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

dnoeth 4628 posts Joined 11/04
29 Jul 2015

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

Dieter

You must sign in to leave a comment.