Is there a Hive equivalent of SQL "not like"

While Hive supports positive like queries: ex开发者_JAVA百科.

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?

Try this:

Where Not (Col_Name like '%whatever%')

also works with rlike:

Where Not (Col_Name rlike '.*whatever.*')

NOT LIKE have been supported in HIVE version 0.8.0, check at JIRA.

https://issues.apache.org/jira/browse/HIVE-1740

In SQL:

select * from table_name where column_name not like '%something%';

In Hive:

select * from table_name where not (column_name like '%something%');

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

Using regexp_extract works as well:

select * from table_name where regexp_extract(my_column, ('myword'), 0) = ''

Actually, you can make it like this:

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

In impala you can use != for not like:

columnname != value

as @Sanjiv answered

hive has support not like

0: hive> select * from dwtmp.load_test;
+--------------------+----------------------+
| load_test.item_id  | load_test.item_name  |
+--------------------+----------------------+
| 18282782           | NW                   |
| 1929SEGH2          | BSTN                 |
| 172u8562           | PLA                  |
| 121232             | JHK                  |
| 3443453            | AG                   |
| 198WS238           | AGS                  |
+--------------------+----------------------+
6 rows selected (0.224 seconds)

0: hive> select * from dwtmp.load_test where item_name like '%ST%';
+--------------------+----------------------+
| load_test.item_id  | load_test.item_name  |
+--------------------+----------------------+
| 1929SEGH2          | BSTN                 |
+--------------------+----------------------+
1 row selected (0.271 seconds)

0: hive> select * from dwtmp.load_test where item_name not like '%ST%';
+--------------------+----------------------+
| load_test.item_id  | load_test.item_name  |
+--------------------+----------------------+
| 18282782           | NW                   |
| 172u8562           | PLA                  |
| 121232             | JHK                  |
| 3443453            | AG                   |
| 198WS238           | AGS                  |
+--------------------+----------------------+
5 rows selected (0.247 seconds)