Check count of repeated digits or characters in Hive column
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
I have a column with numbers in hive. I want to have the count of those rows where digits are repeated and this can be a case for a column of characters also.
Eg-
Output should be 6.
I tried using reverse function of hive but it also gives palindrome numbers.
Sample code which i tried -
select sum(case when Number = reverse(Number) then 1 else 0 end) as counts
from table
where Number is not null
python pandas hive hql
asked Nov 16 '18 at 10:46
nOObdanOObda
366
add a comment |
0
I have a column with numbers in hive. I want to have the count of those rows where digits are repeated and this can be a case for a column of characters also.
Eg-
Output should be 6.
I tried using reverse function of hive but it also gives palindrome numbers.
Sample code which i tried -
select sum(case when Number = reverse(Number) then 1 else 0 end) as counts
from table
where Number is not null
python pandas hive hql
asked Nov 16 '18 at 10:46
nOObdanOObda
366
I am unaware of hiveql. Shall I provide solution in Oracle then may be you can create equivalent query in hiveql
– codeLover
Nov 16 '18 at 11:13
Yeah sure, it will be good. But the problem is with hive which is not as versatile as mysql or oracle
– nOObda
Nov 16 '18 at 11:25
1
Please describe how "6" is calculated. I see seven numbers that have pairs of duplicated digits.
– Gordon Linoff
Nov 16 '18 at 12:00
@GordonLinoff even a single digit should be considered as repeated. And repeated digits can be odd or even in length.
– nOObda
Nov 18 '18 at 5:38
Output is 6 because repeated digits should be standalone such as - 1,2222,33,4444,999999,55. Also number of digits can be odd or even in length.
– nOObda
Nov 18 '18 at 9:29
add a comment |
0
0
0
I have a column with numbers in hive. I want to have the count of those rows where digits are repeated and this can be a case for a column of characters also.
Eg-
Output should be 6.
I tried using reverse function of hive but it also gives palindrome numbers.
Sample code which i tried -
select sum(case when Number = reverse(Number) then 1 else 0 end) as counts
from table
where Number is not null
python pandas hive hql
asked Nov 16 '18 at 10:46
nOObdanOObda
366
I have a column with numbers in hive. I want to have the count of those rows where digits are repeated and this can be a case for a column of characters also.
Eg-
Output should be 6.
I tried using reverse function of hive but it also gives palindrome numbers.
Sample code which i tried -
select sum(case when Number = reverse(Number) then 1 else 0 end) as counts
from table
where Number is not null
python pandas hive hql
python pandas hive hql
asked Nov 16 '18 at 10:46
nOObdanOObda
366
asked Nov 16 '18 at 10:46
nOObdanOObda
366
edited Nov 16 '18 at 11:43
nOObda
asked Nov 16 '18 at 10:46
nOObdanOObda
366
asked Nov 16 '18 at 10:46
nOObdanOObda
366
asked Nov 16 '18 at 10:46
nOObdanOObda
366
366
I am unaware of hiveql. Shall I provide solution in Oracle then may be you can create equivalent query in hiveql
– codeLover
Nov 16 '18 at 11:13
Yeah sure, it will be good. But the problem is with hive which is not as versatile as mysql or oracle
– nOObda
Nov 16 '18 at 11:25
1
Please describe how "6" is calculated. I see seven numbers that have pairs of duplicated digits.
– Gordon Linoff
Nov 16 '18 at 12:00
@GordonLinoff even a single digit should be considered as repeated. And repeated digits can be odd or even in length.
– nOObda
Nov 18 '18 at 5:38
Output is 6 because repeated digits should be standalone such as - 1,2222,33,4444,999999,55. Also number of digits can be odd or even in length.
– nOObda
Nov 18 '18 at 9:29
add a comment |
I am unaware of hiveql. Shall I provide solution in Oracle then may be you can create equivalent query in hiveql
– codeLover
Nov 16 '18 at 11:13
Yeah sure, it will be good. But the problem is with hive which is not as versatile as mysql or oracle
– nOObda
Nov 16 '18 at 11:25
1
Please describe how "6" is calculated. I see seven numbers that have pairs of duplicated digits.
– Gordon Linoff
Nov 16 '18 at 12:00
@GordonLinoff even a single digit should be considered as repeated. And repeated digits can be odd or even in length.
– nOObda
Nov 18 '18 at 5:38
Output is 6 because repeated digits should be standalone such as - 1,2222,33,4444,999999,55. Also number of digits can be odd or even in length.
– nOObda
Nov 18 '18 at 9:29
I am unaware of hiveql. Shall I provide solution in Oracle then may be you can create equivalent query in hiveql
– codeLover
Nov 16 '18 at 11:13
I am unaware of hiveql. Shall I provide solution in Oracle then may be you can create equivalent query in hiveql
– codeLover
Nov 16 '18 at 11:13
Yeah sure, it will be good. But the problem is with hive which is not as versatile as mysql or oracle
– nOObda
Nov 16 '18 at 11:25
Yeah sure, it will be good. But the problem is with hive which is not as versatile as mysql or oracle
– nOObda
Nov 16 '18 at 11:25
1
1
Please describe how "6" is calculated. I see seven numbers that have pairs of duplicated digits.
– Gordon Linoff
Nov 16 '18 at 12:00
Please describe how "6" is calculated. I see seven numbers that have pairs of duplicated digits.
– Gordon Linoff
Nov 16 '18 at 12:00
@GordonLinoff even a single digit should be considered as repeated. And repeated digits can be odd or even in length.
– nOObda
Nov 18 '18 at 5:38
@GordonLinoff even a single digit should be considered as repeated. And repeated digits can be odd or even in length.
– nOObda
Nov 18 '18 at 5:38
Output is 6 because repeated digits should be standalone such as - 1,2222,33,4444,999999,55. Also number of digits can be odd or even in length.
– nOObda
Nov 18 '18 at 9:29
Output is 6 because repeated digits should be standalone such as - 1,2222,33,4444,999999,55. Also number of digits can be odd or even in length.
– nOObda
Nov 18 '18 at 9:29
add a comment |
2 Answers
2
active
oldest
votes
0
You seem to want to know if all digits appear a multiple of 2 times.
One rather painful method is:
select t.*
from t
where ( length(number) - length(replace(number, '0', '')) ) % 2 = 0 and
( length(number) - length(replace(number, '1', '')) ) % 2 = 0 and
. . .
( length(number) - length(replace(number, '9', '')) ) % 2 = 0
answered Nov 16 '18 at 12:05
Gordon LinoffGordon Linoff
794k37318422
Repeated digits can be odd or even in length, this is why i tried using "reverse" but it counts palindrome numbers as well.
– nOObda
Nov 18 '18 at 5:39
add a comment |
0
select count(distinct Number) as dist_numbers
from table
where trim(number) rlike ^0+$|^1+$|^2+$|^3+$|^4+$|^5+$|^6+$|^7+$|^8+$|^9+$;
This is a temporary but cumbersome approach.
answered Nov 18 '18 at 16:24
nOObdanOObda
366
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53336274%2fcheck-count-of-repeated-digits-or-characters-in-hive-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You seem to want to know if all digits appear a multiple of 2 times.
One rather painful method is:
select t.*
from t
where ( length(number) - length(replace(number, '0', '')) ) % 2 = 0 and
( length(number) - length(replace(number, '1', '')) ) % 2 = 0 and
. . .
( length(number) - length(replace(number, '9', '')) ) % 2 = 0
answered Nov 16 '18 at 12:05
Gordon LinoffGordon Linoff
794k37318422
Repeated digits can be odd or even in length, this is why i tried using "reverse" but it counts palindrome numbers as well.
– nOObda
Nov 18 '18 at 5:39
add a comment |
0
You seem to want to know if all digits appear a multiple of 2 times.
One rather painful method is:
select t.*
from t
where ( length(number) - length(replace(number, '0', '')) ) % 2 = 0 and
( length(number) - length(replace(number, '1', '')) ) % 2 = 0 and
. . .
( length(number) - length(replace(number, '9', '')) ) % 2 = 0
answered Nov 16 '18 at 12:05
Gordon LinoffGordon Linoff
794k37318422
Repeated digits can be odd or even in length, this is why i tried using "reverse" but it counts palindrome numbers as well.
– nOObda
Nov 18 '18 at 5:39
add a comment |
0
0
0
You seem to want to know if all digits appear a multiple of 2 times.
One rather painful method is:
select t.*
from t
where ( length(number) - length(replace(number, '0', '')) ) % 2 = 0 and
( length(number) - length(replace(number, '1', '')) ) % 2 = 0 and
. . .
( length(number) - length(replace(number, '9', '')) ) % 2 = 0
answered Nov 16 '18 at 12:05
Gordon LinoffGordon Linoff
794k37318422
You seem to want to know if all digits appear a multiple of 2 times.
One rather painful method is:
select t.*
from t
where ( length(number) - length(replace(number, '0', '')) ) % 2 = 0 and
( length(number) - length(replace(number, '1', '')) ) % 2 = 0 and
. . .
( length(number) - length(replace(number, '9', '')) ) % 2 = 0
answered Nov 16 '18 at 12:05
Gordon LinoffGordon Linoff
794k37318422
answered Nov 16 '18 at 12:05
Gordon LinoffGordon Linoff
794k37318422
answered Nov 16 '18 at 12:05
Gordon LinoffGordon Linoff
794k37318422
answered Nov 16 '18 at 12:05
Gordon LinoffGordon Linoff
794k37318422
794k37318422
Repeated digits can be odd or even in length, this is why i tried using "reverse" but it counts palindrome numbers as well.
– nOObda
Nov 18 '18 at 5:39
add a comment |
Repeated digits can be odd or even in length, this is why i tried using "reverse" but it counts palindrome numbers as well.
– nOObda
Nov 18 '18 at 5:39
Repeated digits can be odd or even in length, this is why i tried using "reverse" but it counts palindrome numbers as well.
– nOObda
Nov 18 '18 at 5:39
Repeated digits can be odd or even in length, this is why i tried using "reverse" but it counts palindrome numbers as well.
– nOObda
Nov 18 '18 at 5:39
add a comment |
0
select count(distinct Number) as dist_numbers
from table
where trim(number) rlike ^0+$|^1+$|^2+$|^3+$|^4+$|^5+$|^6+$|^7+$|^8+$|^9+$;
This is a temporary but cumbersome approach.
answered Nov 18 '18 at 16:24
nOObdanOObda
366
add a comment |
0
select count(distinct Number) as dist_numbers
from table
where trim(number) rlike ^0+$|^1+$|^2+$|^3+$|^4+$|^5+$|^6+$|^7+$|^8+$|^9+$;
This is a temporary but cumbersome approach.
answered Nov 18 '18 at 16:24
nOObdanOObda
366
add a comment |
0
0
0
select count(distinct Number) as dist_numbers
from table
where trim(number) rlike ^0+$|^1+$|^2+$|^3+$|^4+$|^5+$|^6+$|^7+$|^8+$|^9+$;
This is a temporary but cumbersome approach.
answered Nov 18 '18 at 16:24
nOObdanOObda
366
select count(distinct Number) as dist_numbers
from table
where trim(number) rlike ^0+$|^1+$|^2+$|^3+$|^4+$|^5+$|^6+$|^7+$|^8+$|^9+$;
This is a temporary but cumbersome approach.
answered Nov 18 '18 at 16:24
nOObdanOObda
366
edited Nov 18 '18 at 16:53
answered Nov 18 '18 at 16:24
nOObdanOObda
366
answered Nov 18 '18 at 16:24
nOObdanOObda
366
answered Nov 18 '18 at 16:24
nOObdanOObda
366
366
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53336274%2fcheck-count-of-repeated-digits-or-characters-in-hive-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I am unaware of hiveql. Shall I provide solution in Oracle then may be you can create equivalent query in hiveql
– codeLover
Nov 16 '18 at 11:13
Yeah sure, it will be good. But the problem is with hive which is not as versatile as mysql or oracle
– nOObda
Nov 16 '18 at 11:25
1
Please describe how "6" is calculated. I see seven numbers that have pairs of duplicated digits.
– Gordon Linoff
Nov 16 '18 at 12:00
@GordonLinoff even a single digit should be considered as repeated. And repeated digits can be odd or even in length.
– nOObda
Nov 18 '18 at 5:38
Output is 6 because repeated digits should be standalone such as - 1,2222,33,4444,999999,55. Also number of digits can be odd or even in length.
– nOObda
Nov 18 '18 at 9:29