2

I have a hive SQL as below which returns the following output, now I want to get the first not null value from array i.e. "11".

SQ::

select cod_cust, split(reverse(concat_ws('',collect_list(case when acc_status='Dormant' then "1" else "0" end))),'0') status from account_status group by cod_cust;

Output::

cod_cust status

1023 ["","11","1","111","",""]

2209 ["","11","1","111","",""]

Improve this question

1 Answer 1

Reset to default
2

Without using split function the output would be look like:

cod_cust    status
1023        01101011100
2209        01101011100

So the task is to find the first subsequence of 1, which can be solved by regexp_extract. Also you can use IF instead of CASE:

SELECT cod_cust, 
       regexp_extract(reverse(concat_ws('', collect_list(if((acc_status='Dormant'), '1', '0'))), '1+', 0) first_element
status 
FROM account_status 
GROUP BY cod_cust;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks, Iskuskov, I just added the third parameter of regexp_extract as '0' it's working fine for me.... SELECT cod_cust, regexp_extract(reverse(concat_ws('', collect_list(if((acc_status='Dormant'), '1', '0')))), '1+',0) first_element FROM account_status GROUP BY cod_cust; thanks again
@Vivek, you are right! I've updated the post. If my answer is useful, vote up, pleas!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.