In PostgreSQL, 'like' is case-sensitive and 'ilike' is case-insensitive. Does Hive SQL support 'like' and 'ilike' ?
In Hive SQL, the behavior for string matching is a bit different than in PostgreSQL. Here is the breakdown:
1. The LIKE operator
Hive does support LIKE. However, unlike PostgreSQL (where case sensitivity can depend on your collation), Hive’s LIKE is case-sensitive by default.
-- This will only match 'Apple'
SELECT * FROM products WHERE name LIKE 'App%';
2. The ILIKE operator
Hive does not natively support the ILIKE keyword. If you try to use it, Hive will throw a parse error.
How to perform case-insensitive matches in Hive
Since you can't use ILIKE, you have two primary workarounds to achieve the same result:
Option A: Use LOWER() or UPPER()
This is the most common approach. By transforming both sides of the comparison to the same case, you effectively create a case-insensitive search.
-- Equivalent to: name ILIKE 'apple%'
SELECT * FROM products
WHERE LOWER(name) LIKE 'apple%';
Option B: Use RLIKE (Regex Like)
Hive supports RLIKE (or REGEXP), which uses Java-style regular expressions. You can use the case-insensitive flag (?i) at the start of your pattern.
-- The (?i) flag makes the regex case-insensitive
SELECT * FROM products
WHERE name RLIKE '(?i)^apple';
Summary Comparison
| Feature | PostgreSQL | Hive SQL | Hive Alternative |
| Case-Sensitive | LIKE | LIKE | N/A |
| Case-Insensitive | ILIKE | Not Supported | LOWER(col) LIKE 'str' |
| Regex Match | ~ or ~* |
RLIKE / REGEXP |
col RLIKE '(?i)pattern' |