0

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

Sonu
  • 77
  • 11

2 Answers2

1

I've never used hive but it looks to have all the functions I would use in some other DB

How about we replace all the 0 to spaces, rtrim the spaces off the right and rpad with '9' out to the original length, then lpad up to 13 with '0' and then replace any spaces (in the middle, eg if we are converting 101010000 to 000101019999) back to '0'

replace(
  lpad(
    rpad(
      rtrim(
        replace(num, '0', ' ')
      ),
      length(num),
      '9'
    ),
    13,
    '0'
  ),
  ' ',
  '0'
)

As noted, never used hive so there might be some minor syntax things to resolve here - if hive doesn't give you the length of a number by auto converting it to a string, for example, you'll need to stick something in for doing an explicit convert. You could alternatively do the log10 of the number.

I don't know of any fiddle sites that offer hive as a tester..

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • Thanks alot Caius Jard and leftjoin. It worked as expected . I have accepted the answer. However there are some Changes in this scenario , so I have added One new Question for the same. Requesting You. Kindly Help. https://stackoverflow.com/questions/71365625/exclude-minimum-6-digits-and-replace-trailing-digits-in-hive – Sonu Mar 05 '22 at 20:10
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"
leftjoin
  • 36,950
  • 8
  • 57
  • 116