Apache Hive LIKE statement and Pattern Matching Example

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:BigData
  • Reading time:3 mins read

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 Statement

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.