All Forums Database
kallu2020 13 posts Joined 03/14
07 Mar 2014
utf8TO16 : why is it Latin to unicode conversion function when utf8 itself is unicode encoding?

UDF : utf8to16 function:
why is it known as Latin to Unicode conversion function when utf8 and 16 both represent unicode encoding?

dnoeth 4628 posts Joined 11/04
07 Mar 2014

UTF8TO16? There's no such function built-in.
There's only TransUnicodeToUTF8 and TransUTF8ToUnicode, but those are compression UDFs (changing internal storage from UTF6 to UTF8 to save space).
TRANSLATE(col USING LATIN_TO_UNICODE) converts from Latin to Unicode.

Dieter

kallu2020 13 posts Joined 03/14
09 Mar 2014

Thanks Dieter. That helps.

vuyyuru 16 posts Joined 03/11
05 Aug 2015

Hi Dieter,
can you please help in converting data from UTF8 to LATIN1_0A or ISO8859_1
tried using this below query but i am getting error the string contains untranslatable character. In the same way will have to TRANSLATE(COL1 USING UTF8_TO_LATIN1_OA) AS COL1 it says that unknown character sting.
can we do this by writing sql query or not if so how? advice me.
SEL
TRANSLATE(COL1 USING UNICODE_TO_LATIN) AS COL1
FROM
(

SEL
CASE
        WHEN DB.COL1 IS NOT NULL THEN  ' ' || DB.COL1
    ELSE ''
    END
FROM TABLEA
) DT COL1

dnoeth 4628 posts Joined 11/04
05 Aug 2015

If that column contains any non-Latin characters TRANSLATE will fail, you might add WITH ERROR to replace bad chars with an error character (hex '1A'):

TRANSLATE(COL1 USING UNICODE_TO_LATIN WITH ERROR)

 
There's no ISO8859_1 or LATIN1_0A character set in Teradata, only Latin and Unicode.
A session character set might be LATIN1_0A, then the Unicode data is automatically converted.
But if you got Unicode data why do you want to convert it to Latin?

Dieter

vuyyuru 16 posts Joined 03/11
07 Aug 2015

Dieter,
I am connecting to SQLA through ODBC with the session character set UTF8, now i have to write a query to export the data from session character set to another session character set (LATIN1_0A or ISO8859_1) where this data is being used.
That's why trying to export/load the data into LATIN1_0A? any other way? advice
Regards,
Rams

david.craig 73 posts Joined 05/13
07 Aug 2015

Hi vuyyuru,
There are a couple of options:

  1. The Teradata Labs supported approach is to create a Distinct UDT from Unicode which represents ISO8859-1.
  2. The other approach would be the Unicode Toolkit UDFs (on the Dev Exchange) supported by Tak Takahashi, although I don't know if a server form of 8859-1 is supported.

  
-Dave

vuyyuru 16 posts Joined 03/11
07 Aug 2015

Thanks for you inputs, tried with this option. Data looks okay. have to validate the data.

SEL
 TRANSLATE (COL1 USING UNICODE_TO_KANJI1_KANJISJIS
             WITH    ERROR) AS COL1  
FROM
(

SEL
CASE
        WHEN DB.COL1 IS NOT NULL THEN  ' ' || DB.COL1
    ELSE ''
    END
FROM TABLEA
) DT COL1

 

david.craig 73 posts Joined 05/13
10 Aug 2015

I can't recommend that approach as the Kanji1 server character set is no longer supported. What release of the Teradata database is in use?

vuyyuru 16 posts Joined 03/11
10 Aug 2015

TD 14.10

13 Aug 2016

Hi Dieter,
Thanks for sharing the info. One issue am facing currently.
Inserting chinese characters (for eg) into a table with Unicode column is working fine but running the same via worflow is inserting garbage values into table.
We are using informatic and have set ETL connection with UTF8 using Unicode.
Source is obtained from a flat file.
Could you please let me know what could be the issue?
Thanks in advance :)
Regards,
Sai

You must sign in to leave a comment.