使用多个LIKE运算符进行Hive查询 [英] Hive query with multiple LIKE operators

问题描述

  SELECT * 
 FROM some_table 
 WHERE 
 some_col LIKE'％abc％'
 OR 
 some_col LIKE'％xyz％'
或
 some_col LIKE'％pqr％ '
或
 ...（还有一些LIKE语句）
  

我尝试过做以上以及

  WHERE some_col LIKE'％abc | pqr | xyz％'
  

但他们没有返回任何结果。如果我编写单独的查询，即可以正常工作，即：

  WHERE some_col LIKE'％abc％' - >返回结果
  

和

  WHERE some_col LIKE'％pqr％' - >也返回结果
  

解决方案

来自文档：

如果A或B为NULL，则返回NULL，如果有则返回TRUE（可能为空）

A RLIKE B

）A
的子串与Java正则表达式B匹配，否则为FALSE。例如，
'foobar'RLIKE'foo'评估为TRUE，'foobar'RLIKE
'^ f。* r $'也是如此。

< hr>

A REGEXP B

与RLIKE相同

因此，使用

  WHERE some_col RLIKE'abc | pqr | xyz'
  

What would be the right way to write a Hive query with multiple LIKE operators like this:

SELECT * 
FROM some_table
WHERE
some_col LIKE '%abc%'
OR
some_col LIKE '%xyz%'
OR
some_col LIKE '%pqr%'
OR
... (some more LIKE statements)

I tried doing the above as well as

WHERE some_col LIKE '%abc|pqr|xyz%' 

but they didn't return any results. It works fine if I write separate queries, i.e.

WHERE some_col LIKE '%abc%' -> returns results

and

WHERE some_col LIKE '%pqr%' -> also returns results

解决方案

From the docs:

A RLIKE B

NULL if A or B is NULL, TRUE if any (possibly empty) substring of A matches the Java regular expression B, otherwise FALSE. For example, 'foobar' RLIKE 'foo' evaluates to TRUE and so does 'foobar' RLIKE '^f.*r$'.

---

A REGEXP B

Same as RLIKE.

So, use

WHERE some_col RLIKE 'abc|pqr|xyz' 

这篇关于使用多个LIKE运算符进行Hive查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！