CODEWITHSUNDEEP
sqlhadoophivehiveql
forsaken
forsaken

Reputation: 709

Character in last position only to be replaced - Hive

I have a situation where I want to replace only the last character in a column in hive based on certain conditions. My code is as follows,

select 
case 
    when amtsv111 >= 0 then 
    case 
        when substring(amtsv111,-1,1) = '0' then regexp_replace(amtsv111,substring(amtsv111,-1,1),'{')
        when substring(amtsv111,-1,1) = '1' then regexp_replace(amtsv111,substring(amtsv111,-1,1),'A')
        end

So for this code, I get result as:

15101    A5A0A

Whereas I want the result as follows:

15101    1510A

Can someone please help me with the same?

Upvotes: 0

Views: 1425

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can just do:

select (case when amtsv111 like '%0' then concat(substring(amtsv111, 1, length(amtsv111) - 1), '{'
             when amtsv111 like '%1' then concat(substring(amtsv111, 1, length(amtsv111) - 1), 'A'
             else amtsv111
        end)

Upvotes: 2

Related Questions