Search code examples
syntaxhivesql-like

Is there a Hive equivalent of SQL "not like"

While Hive supports positive like queries: ex.

select * from table_name where column_name like 'root~%';

Hive Does not support negative like queries: ex.

select * from table_name where column_name not like 'root~%';

Does anyone know an equivalent solution that Hive does support?

Solution

  • Check out https://cwiki.apache.org/confluence/display/Hive/LanguageManual if you haven't. I reference it all the time when I'm writing queries for hive.

    I haven't done anything where I'm trying to match part of a word, but you might check out RLIKE (in this section https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#Relational_Operators)

    This is probably a bit of a hack job, but you could do a sub query where you check if it matches the positive value and do a CASE (http://wiki.apache.org/hadoop/Hive/LanguageManual/UDF#Conditional_Functions) to have a known value for the main query to check against to see if it matches or not.

    Another option is to write a UDF which does the checking.

    I'm just brainstorming while sitting at home with no access to Hive, so I may be missing something obvious. :)

    Hope that helps in some fashion or another. \^_^/

    EDIT: Adding in additional method from my comment below.

    For your provided example colName RLIKE '[^r][^o][^o][^t]~\w' That may not be the optimal REGEX, but something to look into instead of sub-queries