LIKE Pattern Matching
Vertica supports LIKE
pattern-matching conditions in subqueries and take the following form:
string-expression [ NOT ] { LIKE | ILIKE | LIKEB | ILIKEB } string-expression
The following command searches for customers whose company name starts with "Ev" and returns the total count:
=> SELECT COUNT(*) FROM customer_dimension WHERE customer_name LIKE (SELECT 'Ev%' FROM customer_dimension LIMIT 1); count ------- 153 (1 row)
Vertica also supports single-row subqueries as the pattern argument for LIKEB and ILIKEB predicates; for example:
=> SELECT * FROM t1 WHERE t1.x LIKEB (SELECT t2.x FROM t2);
The following symbols are substitutes for the LIKE keywords:
~~ LIKE ~# LIKEB ~~* ILIKE ~#* ILIKEB !~~ NOT LIKE !~# NOT LIKEB !~~* NOT ILIKE !~#* NOT IILIKEB
Note: The ESCAPE
keyword is not valid for the above symbols.
See LIKE-predicate in the SQL Reference Manual for additional examples.