CODEWITHSUNDEEP
sqlhadoophivewhere-clausesql-like
Logan
Logan

Reputation: 293

SQL Hive Hadoop - Multiple Wildcard Values with Like Statement

HIVE/Hadoop - How would I create a query with multiple wildcard values using the like statement (I tried similar to command but that did not work).

Example query

select *
from schema.table a
where a.apple = '1'
and a.berry LIKE ('A10%','B10%')
;

The above did not work. I did try replacing LIKE with IN but the query refuses to stop running, not sure why the IN command is causing for the query to keep running and not even produce a result. Is using OR my only option here? I have about 15 values I would like wildcard values on.

As a side note question to better help me understand the above problem, I noticed my query running with no end time when using:

and a.berry IN ('A10%')

versus

and a.berry LIKE 'A10%'

Should I always use LIKE with the wildcard %?

Upvotes: 0

Views: 4186

Answers (1)

GMB
GMB

Reputation: 222542

LIKE and IN can't do what you want: LIKE operates on scalar operands, while IN does not do pattern matching.

You need to repeat the expression:

and (a.berry like 'A10%' or a.berry like 'B10%')

Another option is to use a regex - this is shorter to type when the list of values gets longer:

and a.berry rlike '^(A10)|(B10)'

Or:

and a.berry rlike '^[AB]10'

Upvotes: 1

Related Questions