1

Can someone help me to write below logic in hive. I have one value in column in which I have digits with trailing 0's. I need to Replace all these 0's by 9,while replacing the 0's by 9 ,I need to also consider that before 9 minimun 6 digits should be there, else need to exclude some 0's so that before 9 can have at-least 6 digits.PFB some Scenarios.

  1. 1234506600000000000

Here we can see the number of digits before trailing 0's is 8 (12345066) so i Just need to remaining 0's by 9 and the output will be like. 1234506699999999999.

  1. 1234500000000000000

Here I have only 5 digits before trailing 0's so I need to consider 6th Position's 0 also a digit and need to exclude this while replacing the 0's by 9 so the output will be 1234509999999999999.

  1. 1000000000000000000

Here I have only 1 digit before trailing 0's ,so I need to exclude 5 extra 0's and need to replace remaining 0's by 9, so final output will be like 1000009999999999999.

 Input                        Output

1234506600000000000        1234506699999999999
1234500000000000000        1234509999999999999
1000000000000000000        1000009999999999999
Sonu
  • 77
  • 11
  • Why would you want to do something like that? – David דודו Markovitz Mar 06 '22 at 10:35
  • @DavidדודוMarkovitz Actually I have this input in hive table...and I need to generate max range for that number so replacing 0's by '9' for minimum 6 digits or digit before 0 need to consider as satstic value, after these digit only need to generate high range. – Sonu Mar 07 '22 at 04:15

1 Answers1

1

If you want to modify leftjoin's technique from the other question we can tweak the Regex to match at least 6 digits including 0s

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{6,}?)(0+)$','$1|$2'),'\\|') splitted
  from mytable
)s

If you want to go the replace/pad/replace route I proposed, you'd check the length of the number after it's rtrim'd and if it's less than 6, rpad it out to 6 with zeroes. Most implementations of rpad would chop the string off at 6 chars if it were longer than 6 - if they didn't it would be nice and simple to just call rpad after rtrim. It might be worth making your own rpad function that leaves strings longer than N alone, if hive's rpad performs a substring op

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • Thanks a lot @Caius Jard. It worked. I have accepted the answer. Is there any way to Join these CTE logic with another Table.? I am trying to join it with one more table.But looks like the syntax is not supporting. https://stackoverflow.com/questions/71405228/hive-join-with-cte – Sonu Mar 09 '22 at 06:45