LIKE predicate

Retrieves rows where the string value of a column matches a specified pattern. The pattern can contain one or more wildcard characters.

Syntax

string‑expression [ NOT ] { LIKE | ILIKE | LIKEB | ILIKEB } 'pattern' [ESCAPE 'escape‑character' ]

Parameters

string‑expression

The column values to search for pattern.

NOT

Returns true if LIKE returns false, and the reverse; equivalent to NOT string LIKE pattern.

pattern

Specifies what strings to match, typically containing one or both of the following wildcard characters:

  • Underscore (_) matches any single character.
  • Percent sign (%) matches any string of zero or more characters.
ESCAPE escape‑character

Specifies an escape character, used in the to escape reserved characters underscore (_), percent (%), and the escape character itself. This is enforced only for non-default collations.

If you omit this parameter, you can use Vertica's default escape character, backslash (\), which is valid for CHAR and VARCHAR strings.

Backslash is not valid for binary data types character. To embed an escape character for binary data types, use ESCAPE to specify a valid binary character.

Substitute Symbols

You can substitute the following symbols for LIKE and its variants:

~~ LIKE
~# LIKEB
~~* ILIKE
~#* ILIKEB
!~~ NOT LIKE
!~# NOT LIKEB
!~~* NOT ILIKE
!~#* NOT ILIKEB

ESCAPE is not valid for the above symbols.

Pattern Matching

LIKE requires that the entire string expression match the pattern. To match a sequence of characters anywhere within a string, the pattern must start and end with a percent sign.

LIKE does not ignore trailing white space characters. If the data values to match end with an indeterminate amount of white space, append the wildcard character % to pattern.

LIKE Variants Compared

The LIKE predicate is compliant with the SQL standard. Vertica also supports several non-standard variants, notably ILIKE , which is equivalent to LIKE except it performs case-insensitive searches. The following differences pertain to LIKE and its variants:

  • LIKE operates on UTF-8 character strings. Exact behavior depends on collation parameters such as strength. In particular, ILIKE works by setting S=2 (ignore case) in the current session locale.
  • LIKE and ILIKE are stable for character strings, but immutable for binary strings, while LIKEB and ILIKEB are immutable for both cases.
  • LIKEB and ILIKEB predicates do byte-at-a-time ASCII comparisons.

Locale Dependencies

In the default locale, LIKE and ILIKE handle UTF-8 character-at-a-time, locale-insensitive comparisons. ILIKE handles language-independent case-folding.

In non-default locales, LIKE and ILIKE perform locale-sensitive string comparisons, including some automatic normalization, using the same algorithm as the "=" operator on VARCHAR types.

ESCAPE expressions evaluate to exactly one octet—or one UTF-8 character for non-default locales.

Examples

The following example illustrates pattern matching in locales.

\locale default=> CREATE TABLE src(c1 VARCHAR(100));
=> INSERT INTO src VALUES (U&'\00DF'); --The sharp s (ß)
=> INSERT INTO src VALUES ('ss');
=> COMMIT;

Querying the src table in the default locale returns both ss and sharp s.

=> SELECT * FROM src;
 c1 
----
 ß
 ss
(2 rows)

The following query combines pattern-matching predicates to return the results from column c1:

=> SELECT c1, c1 = 'ss' AS equality, c1 LIKE 'ss' 
   AS LIKE, c1 ILIKE 'ss' AS ILIKE FROM src;
 c1 | equality | LIKE | ILIKE
----+----------+------+-------
 ß  | f        | f    | f
 ss | t        | t    | t
(2 rows)

The next query specifies unicode format for c1:

=> SELECT c1, c1 = U&'\00DF' AS equality,
   c1 LIKE U&'\00DF' AS LIKE,
   c1 ILIKE U&'\00DF' AS ILIKE from src;
 c1 | equality | LIKE | ILIKE
----+----------+------+-------
 ß  | t        | t    | t
 ss | f        | f    | f
(2 rows)

Now change the locale to German with a strength of 1 (ignore case and accents):

\locale LDE_S1
=> SELECT c1, c1 = 'ss' AS equality, 
c1 LIKE 'ss' as LIKE, c1 ILIKE 'ss' AS ILIKE from src;
 c1 | equality | LIKE | ILIKE
----+----------+------+-------
 ß  | t        | t    | t
 ss | t        | t    | t
(2 rows)

This example illustrates binary data types with pattern-matching predicates:

=> CREATE TABLE t (c BINARY(1));
=> INSERT INTO t values(HEX_TO_BINARY('0x00'));
=> INSERT INTO t values(HEX_TO_BINARY('0xFF'));
=> SELECT TO_HEX(c) from t;
 TO_HEX
--------
 00
 ff
(2 rows)
select * from t;
  c
------
 \000
 \377
(2 rows)
=> SELECT c, c = '\000', c LIKE '\000', c ILIKE '\000' from t;
  c   | ?column? | ?column? | ?column?
------+----------+----------+----------
 \000 | t        | t        | t
 \377 | f        | f        | f
(2 rows)
=> SELECT c, c = '\377', c LIKE '\377', c ILIKE '\377' from t;
  c   | ?column? | ?column? | ?column?
------+----------+----------+----------
 \000 | f        | f        | f
 \377 | t        | t        | t
(2 rows)