generate Highest Number from the given Input and replacing 0 by 9 using hive

Question

Can Someone Help/suggest me how should I handle below scenario in hive.

I have one column which contain some values in which I have 0's after some digits(after 6 digits) I need to replace all these 0 by 9. and if i have 0's after 5 digits then i need to include 0 at the starting and then again need to replace 0's which comes after 6 digits.PFB some sample records and expected output.

       Input                    output
    1234560000000             1234569999999
     123450000000             0123459999999
      12340000000             0012349999999
       1230000000             0001239999999

so here basically I need to check from right to left.ie. 1234560000000 here I will start checking from right (0) and as soon as find any digit i will replace all the 0's by 9 and if digit count other then 0 is less than 6 then will add 0 in the beginning.

kindly suggest

Answer 1

Split string on two parts: digits at the end and everything else, replace zeroes with 9, concatenate.

Demo:

with mytable as (
select '1234560000000' as input union all
select '123450000000' union all
select '12340000000' union all
select '1230000000'   
)

select lpad(concat(splitted[0], translate(splitted[1],'0','9')),13,0)
from
(
select split(regexp_replace(input,'(\\d*?)(0+)$','$1|$2'),'\\|') splitted
  from mytable
)s

Result

"1234569999999"
"0123459999999"
"0012349999999"
"0001239999999"