CODEWITHSUNDEEP
hivesql-like
Shobit
Shobit

Reputation: 794

Hive query with multiple LIKE operators

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

Upvotes: 12

Views: 61577

Answers (5)

BalaramRaju
BalaramRaju

Reputation: 439

I believe that the issue might be you need to group the like statement. Your Example done:

SELECT * 
FROM some_table
WHERE
(some_col LIKE '%abc%'
OR
some_col LIKE '%xyz%'
OR
some_col LIKE '%pqr%')

Upvotes: 4

Samson Scharfrichter
Samson Scharfrichter

Reputation: 9067

If all you need is to check for a list of specific substrings, you could use a different approach e.g.

WHERE InStr(some_col, 'abc') +InStr(some_col, 'pqr') +... >0

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-StringFunctions

Upvotes: 0

Shivanand Pawar
Shivanand Pawar

Reputation: 547

You can probably use rlike(regular_expression).

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

Upvotes: 13

mattinbits
mattinbits

Reputation: 10428

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'

Upvotes: 21

Rahul Tripathi
Rahul Tripathi

Reputation: 172468

You can try to use UNION if you have to use multiple condition in LIKE like this:

SELECT * FROM some_table WHERE some_col LIKE '%abc%'
UNION
SELECT * FROM some_table WHERE some_col LIKE '%xyz%'
UNION
SELECT * FROM some_table WHERE some_col LIKE '%pqr%'

Upvotes: 1

Related Questions