CODEWITHSUNDEEP
hivehiveql
JKC
JKC

Reputation: 2618

How to check whether a string pattern is present in a hive table column

I am using the Cloudera cluster containing Hive-0.13.1 for my day-to-day activities.

I have few string patterns with me and I need to find whether any of these patterns are present in one of the columns in Hive table.

Example: The string patterns I have is short names of cricketers such as "Dravid", "Sachin", "Ganguly". I have a column in the hive which contains the full names of all the cricketers.

select full_name from players limit 3;

Rahul Dravid
Sachin Ramesh Tendulkar
Sourav Ganguly

I need to compare my string patterns with the values returned by my query and create another column with "Y" if it matches or with "N" if it doesn't match.

How to write Hive query to accomplish this apart from Regex?

Upvotes: 0

Views: 2300

Answers (2)

Iskuskov Alexander
Iskuskov Alexander

Reputation: 4375

Shorter version of @Shu answer using conditional function if and relational operator RLIKE for regular expression matching:

SELECT full_name, 
if((lower(full_name) RLIKE 'dravid|sachin|ganguly'), 'Y', 'N') AS is_match
FROM players;

Upvotes: 0

notNull
notNull

Reputation: 31540

You can use Case statement with like, or operators

in the below query i have used lower function on full_name field and compared the data in the filed with lower names(dravid,sachin).

Query:- 

select full_name, 
case 
    lower(full_name) like '%dravid%' 
or 
    lower(full_name) like '%ganguly%' 
or 
    lower(full_name) like '%sachin%' 
    then 'Y' 
else 'N' 
end new_column_name 
from players limit 3;

Upvotes: 0

Related Questions