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; 

Answer 1

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')

Answer 2

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:

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF