CODEWITHSUNDEEP
stringhive
user2978621
user2978621

Reputation: 813

hive check comma separated String contains a string

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;

Upvotes: 21

Views: 55041

Answers (3)

MukeshKoshyM
MukeshKoshyM

Reputation: 554

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'

Upvotes: 0

libjack
libjack

Reputation: 6443

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

Upvotes: 31

Neels
Neels

Reputation: 2543

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

Upvotes: 23

Related Questions