排除最少 6 位数字并替换 Hive 中的尾随数字

发布于 2025-01-12 09:45:05 字数 767 浏览 3 评论 0原文

有人可以帮我在配置单元中编写以下逻辑吗？我在列中有一个值，其中的数字尾随 0。我需要将所有这些 0 替换为 9，同时将 0 替换为 9 ，我还需要考虑在 9 之前至少应该有 6 位数字，否则需要排除一些 0，以便 9 之前至少可以有 6 位数字。 PFB 一些场景。

1234506600000000000

这里我们可以看到尾随 0 之前的位数是 8 (12345066)，所以我只需要把 0 保留 9 个，输出就会像这样。 1234506699999999999。

1234500000000000000

这里我在尾随 0 之前只有 5 位数字，所以我需要考虑第 6 个位置的 0 也是一个数字，并且需要在用 9 替换 0 时排除它，因此输出将为 1234509999999999999。

1000000000000000000

这里我在尾随 0 之前只有 1 位数字，所以我需要排除 5 个额外的 0，并且需要将剩余的 0 替换为 9，所以最终输出将类似于 1000009999999999999。

 Input                        Output

1234506600000000000        1234506699999999999
1234500000000000000        1234509999999999999
1000000000000000000        1000009999999999999

原文

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.

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.

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.

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

平生欢 2025-01-19 09:45:05

如果您想修改另一个问题中的 leftjoin 技术，我们可以调整正则表达式以匹配至少 6 位数字（包括 0）

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+)
如果您想走我建议的替换/填充/替换路线，你会在 rtrim'd 后检查数字的长度，如果它小于 6，则用零将其填充到 6。如果字符串长度超过 6 个字符，rpad 的大多数实现都会将其截断为 6 个字符 - 如果不这样做，那么在 rtrim 之后调用 rpad 会很好而且很简单。如果 hive 的 rpad 执行子字符串操作，那么创建自己的 rpad 函数可能值得，该函数可以单独留下比 N 长的字符串
,'$1|$2'),'\\|') splitted
  from mytable
)s

如果您想走我建议的替换/填充/替换路线，你会在 rtrim'd 后检查数字的长度，如果它小于 6，则用零将其填充到 6。如果字符串长度超过 6 个字符，rpad 的大多数实现都会将其截断为 6 个字符 - 如果不这样做，那么在 rtrim 之后调用 rpad 会很好而且很简单。如果 hive 的 rpad 执行子字符串操作，那么创建自己的 rpad 函数可能值得，该函数可以单独留下比 N 长的字符串

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+)
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
,'$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

回复收藏 0 原文

~没有更多了~