Hive query with multiple LIKE operators

Question

What would be the right way to write a Hive query with multiple LIKE operators like this:

SELECT * 
FROM some_table
WHERE
some_col LIKE '%abc%'
OR
some_col LIKE '%xyz%'
OR
some_col LIKE '%pqr%'
OR
... (some more LIKE statements)

I tried doing the above as well as

WHERE some_col LIKE '%abc|pqr|xyz%' 

but they didn't return any results. It works fine if I write separate queries, i.e.

WHERE some_col LIKE '%abc%' -> returns results

and

WHERE some_col LIKE '%pqr%' -> also returns results

Answer 1

From the docs:

A RLIKE B

NULL if A or B is NULL, TRUE if any (possibly empty) substring of A matches the Java regular expression B, otherwise FALSE. For example, 'foobar' RLIKE 'foo' evaluates to TRUE and so does 'foobar' RLIKE '^f.*r$'.

---

A REGEXP B

Same as RLIKE.

So, use

WHERE some_col RLIKE 'abc|pqr|xyz' 

Answer 2

You can probably use rlike(regular_expression).

WHERE some_col RLIKE '*abc*|*pqr*|*xyz*'