Search code examples
hivenulldecodetranslate

Null value is also decoding in HIVE

I am working on Hive. I need decode some of the fields in table so i am using Translate() method but problem is like in some of the places i have null this character also decoding. How do you solve this one?

here is my code

TRANSLATE (Address2, 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', 'DEFGHIJKLMNOPQRSTUVWXYZABCdefghijklmnopqrstuvwxyzabc') as Address2,

Solution

  • use a CASE block 

    Select
    CASE WHEN Address2 IS NOT NULL  THEN
      TRANSLATE (Address2,
                  'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',
                  'DEFGHIJKLMNOPQRSTUVWXYZABCdefghijklmnopqrstuvwxyzabc') 
     END as 
Address2