CODEWITHSUNDEEP
sqlarrayshivehiveqlsql-like
trougc
trougc

Reputation: 405

Is there a way to use like operator with an array in HiveQL?

I am looking for a way to query a collect_set/list column with multiple values using like operator. In the below example I am looking to get the rows that have one of the values like '121%'

id values
1 ["8001","12100"]
2 ["12134","9999","2222"]
3 NULL
4 ["5671","9765]

Result:

id values
1 ["8001","12100"] -- because of 121 in 2nd value of the collect_set
2 ["12134","9999","2222"] -- because of 121 in first value of the collect_set

Any help will be appreciated. Thanks.

Upvotes: 2

Views: 645

Answers (1)

leftjoin
leftjoin

Reputation: 38335

Concatenate array using some delimiter, for example | and use concatenated string in RLIKE operator.

Demo:

with mytable as (
select 1 id, array('8001','12100') as `values`
union all
select 2, array('12134','9999','2222')
union all
select 3, array() 
union all
select 4, array('5671','9765') 
)
select * from mytable
where concat('|',concat_ws('|',`values`),'|') rlike '\\|121'

Result:

id  values
1   ["8001","12100"]
2   ["12134","9999","2222"]

Note: Pipe | in regex needs to be escaped with double backslash.

Upvotes: 2

Related Questions