CODEWITHSUNDEEP
syntaxhivesql-like
CMaury
CMaury

Reputation: 1283

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?

Upvotes: 26

Views: 73663

Answers (8)

huang botao
huang botao

Reputation: 416

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)

Upvotes: 0

DrSD
DrSD

Reputation: 151

In impala you can use != for not like: 

columnname != value

Upvotes: 0

Reid Minto
Reid Minto

Reputation: 181

Using regexp_extract works as well:

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

Upvotes: 1

Sanjiv
Sanjiv

Reputation: 1815

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

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

Upvotes: 8

minhas23
minhas23

Reputation: 9671

In SQL:

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

In Hive:

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

Upvotes: 6

zorro
zorro

Reputation: 1

Actually, you can make it like this:

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

Upvotes: 0

QuinnG
QuinnG

Reputation: 6424

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

Upvotes: 3

HAL9000
HAL9000

Reputation: 551

Try this:

Where Not (Col_Name like '%whatever%')

also works with rlike:

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

Upvotes: 55

Related Questions