Unlike various relational databases such as Netezza, Teradata, Oracle etc, Apache hive support pattern matching using LIKE, RLIKE or INSTR functions. You can search for string by matching patterns. Note that, Hive LIKE statement is case-sensitive.
Apache Hive LIKE statements returns TRUE if string that you are searching for. The Hive NOT LIKE is negation of LIKE and vice-versa.
Related reading:
Hive LIKE Statement Patterns Matching
If the string does not contain any percentage sign or underscore, then pattern is the string itself, in this case LIKE acts as an equality operator.
‘abc’ LIKE ‘abc’ -> True : Equality operation’abc’ LIKE ‘a%’ -> True : Pattern matching. Return everything that starts with ‘a”abc’ LIKE ‘_b_’ -> True : Any single character matching ‘abc’ LIKE ‘c’ -> False: does not match any condition
LIKE pattern matches always include the entire string. To match a pattern anywhere within a string, the pattern must start and end with a percent sign.
Apache Hive LIKE Statement Examples
Below is example showing various option in LIKE operator:
https://gist.github.com/17a52eb4e39491e1fe29caa3a505bf31
Hive LIKE statement in WHERE clause
Hive LIKE statement is similar to regular relational database LIKE statements. You can refer SQL-1999 LIKE command document for more information of that.
Below example statement demonstrates the use of Hive LIKE statements in WHERE clause:
https://gist.github.com/42517612ed39b2c721f6683bae3c23f9
Hive LIKE statement Escape Characters
You can escape the special character in Hive LIKE statements using ‘\’. This feature allows you to escape the string with special character. For example, Hive table column value may contain a string that has embedded percentage (%) sign, in that case escape character functionality will allow you to ignore those during string matching.
Below example statements show usage:
https://gist.github.com/5fcdb44fa83101632c6b9821f4e0e7ea
Apache Hive RLIKE statement
You can match the pattern using regular expression with help of Hive RLIKE statement.
Below example demonstrates the Hive RLIKE statement:
https://gist.github.com/fa7cbccaa8a7c459eb6153999d3cf0b8
You can use Hive RLIKE wherever LIKE statement is allowed. only thing you should remember is the patter that you are looking for.