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 |
pattern
|
Specifies what strings to match, typically containing one or both of the following wildcard characters:
|
ESCAPE escape‑character
|
Specifies an escape character, used in the to escape reserved characters underscore ( If you omit this parameter, you can use Vertica's default escape character, backslash (\), which is valid for Note: Backslash is not valid for binary data types character. To embed an escape character for binary data types, use |
Substitute Symbols
You can substitute the following symbols for LIKE
and its variants:
~#
|
LIKEB
|
~~*
|
ILIKE
|
~#*
|
ILIKEB
|
!~~
|
NOT LIKE
|
!~#
|
NOT LIKEB
|
!~~*
|
NOT ILIKE
|
!~#*
|
NOT ILIKEB
|
Note: 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
andILIKE
are stable for character strings, but immutable for binary strings, whileLIKEB
andILIKEB
are immutable for both cases.LIKEB
andILIKEB
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)