Search code examples
hivehexteradata

Both '00'XC in teradata and hive

I found the following query in teradata and there is no error when I run it.

Trim(Both '00'XC FROM STG_101_104_BORM_NEW.NO_TRN) AS NO_TRN

what is the use of Both '00'XC function? when I run it on hive there is an error, what should be changed when we translate it to hive?

Solution

  • Trim(Both '00'XC FROM STG_101_104_BORM_NEW.NO_TRN) will work in Teradata and it removes leading and trailing null hexadecimal characters from the string. '00'XC - is a null character. XC indicates a hexadecimal string literal and 00 is null in hex.
    So trim() is trying to remove it but this will work in teradata but not in hive. You need to know the purpose of this function in teradta and then implement accordingly in hive.
    I explained how trim() is different in hive and teradata below.

    trim(origstr) in hive just removes spaces from left and right of a string.
    trim(both/leading/trailing patternstr from origstr)) in Teradata also removes spaces(and other strings) from the string but it needs more arguments. its more powerful because it can remove bytes/hex characters as well etc. Teradata-Trim For example in hive-
    SELECT TRIM(' aabccd ') FROM t2; - will result aabccd

    For example in teradata-
    SELECT TRIM(BOTH FROM ' aabccd ') FROM t2; - will result aabccd
    SELECT TRIM(LEADING 'a' FROM 'aabccd') FROM t2; - will result bccd

    EDIT : please use to remove leading and trailing 0.

    select REGEXP_REPLACE(REGEXP_REPLACE('000ABCA000','0+$',''),"^0+(?!$)",'')

    '0+$' - this will remove trailing 0s.
    ^0+(?!$) - this will remove leading 0s.