6706: Untranslatable Characters in Teradata

800 views Asked by At

I have following 2 strings consisting of Arabic characters, such strings fails' my mloads on daily basis:

شركة جيبكا السعودية
ح717695 

Both columns in which these 2 strings are inserted are defined as Unicode. And in case of these string translate_chk does not work either.

Following is the select statement I am using:

select Translate_Chk ( 'شركة جيبكا السعودية' using LATIN_TO_UNICODE WITH ERROR)

Moreover, I wrote following code for a finnish client and it worked fine. But it does not work in case of Arabic characters.

SELECT (CASE WHEN Translate_Chk ( TRIM('شركة جيبكا السعودية') using LATIN_TO_UNICODE ) <> 0
THEN
        CASE WHEN (regexp_Instr('شركة جيبكا السعودية', 
        '\x{00}|\x{01}|\x{02}|\x{03}|\x{04}|\x{05}|\x{06}|\x{07}|\x{08}|\x{09}|\x{0B}|\x{0C}|\x{0E}|\x{0F}|\x{10}|\x{11}|\x{12}|\x{13}|\x{14}|\x{15}|\x{16}
        |\x{17}|\x{18}|\x{19}|\x{1A}|\x{1B}|\x{1C}|\x{1D}|\x{1E}|\x{1F}|\x{5E}|\x{60}|\x{7C}|\x{7E}|\x{7F}|\x{A0}|\x{A2}|\x{A6}|\x{A8}|\x{A9}|\x{AA}|\x{AB}|\x{AC}
        |\x{AD}|\x{AE}|\x{AF}|\x{B0}|\x{B1}|\x{B2}|\x{B3}|\x{B4}|\x{B5}|\x{B6}|\x{B7}|\x{B8}|\x{B9}|\x{BA}|\x{BB}|\x{BC}|\x{BD}|\x{BE}|\x{F0}',1,1,0,
            'c')) >0
            THEN
            ''
        when TD_SYSFNLIB.InSTR('شركة جيبكا السعودية', U&'\008A' UESCAPE '\')>0 
        THEN
        ''
    WHEN TD_SYSFNLIB.InSTR('شركة جيبكا السعودية', U&'\009A' UESCAPE '\')>0
    THEN
    ''
        WHEN TD_SYSFNLIB.InSTR('شركة جيبكا السعودية', U&'\008C' UESCAPE '\') >0
        THEN
        ''
        WHEN TD_SYSFNLIB.InSTR('شركة جيبكا السعودية', U&'\009C' UESCAPE '\')>0 
        THEN
        ''
        WHEN TD_SYSFNLIB.InSTR('شركة جيبكا السعودية', U&'\009F' UESCAPE '\')>0
        THEN
        ''
        END
ELSE
'String' END);

Teradata Verion is 16.20.53.31

How can we replace such strings with null? I donot care about data loss as currently I delete such records from the file and resume my mload.

0

There are 0 answers