hive check comma separated String contains a string

Question

I have a column in hive table list_ids which is a list of ids stored as comma separated string.

how can I write a query for this column to check if it stores a particular id

Example:

 list_ids = "abc,cde,efg"

I want to something like

 select * from table_name where list_ids contains cde;

score 31 · Accepted Answer · answered Apr 02 '14 at 18:46

31

Use Hive standard functions split and array_contains

split(string str, string pat) returns array<string> by splitting str around pat (regular expression)

array_contains(array<T>, value) returns true if array contains value

select * from table_name where array_contains(split(list_ids,','),'cde')

answered Apr 02 '14 at 18:46

libjack

2

Is there a away to do multiple values? i.e. array_contains(split(list_ids,','), 'cde|abc|xyz') – Chenna V Jun 12 '15 at 19:41
@blueskin First split the array and then do as many contains as you wish: `from (select split(list_ids,',') as ids_array from table_name) where array_contains(ids_array, 'abc') or array_contains(ids_array, 'xyz');` – logi-kal Sep 18 '20 at 10:45

score 23 · Answer 2 · answered Mar 25 '14 at 10:34

23

Hive supports LIKE operator. You can do it easily by using:

select * from table_name where list_ids like '%cde%';

Check out this language manual for more info:

answered Mar 25 '14 at 10:34

Neels

2

just better to write `'%,cde,%'` so ids like `'cdee'` wont return – dimamah Mar 26 '14 at 09:30
3

@dimamah Unfortunately, '%,abc,%' won't match the example in the OP, because the first item in the list won't have a comma in front. Similarly, the last item won't have a comma behind it. – Tom Panning Apr 24 '14 at 16:50
2

Thats right. so we can do : `select * from table_name where concat(',',list_ids,',') like '%,cde,%';` – dimamah May 09 '14 at 09:28

score 0 · Answer 3 · answered May 04 '18 at 17:30

0

Use Hive function explode you can achieve this.

Example

select *
from table_name
LATERAL VIEW explode(list_ids) exploded_table as list_id_tbl
where list_ids='cde'

answered May 04 '18 at 17:30

MukeshKoshyM

3 Answers3