Categories:
String & Binary Functions (Matching/Comparison)

LIKE ANY

Allows case-sensitive matching of strings based on comparison with one or more patterns.

The operation is similar to LIKE. If the input string matches any of the patterns, this returns the input string.

See also:
LIKE

Syntax

<subject> LIKE ANY (<pattern1> [, <pattern2> ... ] ) [ ESCAPE <escape_char> ]

Arguments

Required:

subject
The string to compare to the pattern(s).
pattern#
The pattern(s) that the string is to be compared to. You must specify at least one pattern.

Optional:

escape_char
Character(s) inserted in front of a wildcard character to indicate that the wildcard should be interpreted as a regular character rather than as a wildcard.

Returns

The data type of the returned value is VARCHAR.

Usage Notes

  • SQL wildcards are supported in pattern:
    • An underscore (_) matches any single character.
    • A percent sign (%) matches any sequence of zero or more characters.
  • Wildcards in pattern include newline characters (\n) in subject as matches.
  • The pattern is considered a match if the pattern matches the entire input string (subject). To match a sequence anywhere within a string, start and end the pattern with %, for example ‘%something%’.
  • NULL does not match NULL. In other words, if the subject is NULL and one of the patterns is NULL, that is not considered a match.

Examples

Create a table that contains some strings:

CREATE OR REPLACE TABLE like_example(subject varchar(20));
INSERT INTO like_example VALUES
    ('John  Dddoe'),
    ('Joe   Doe'),
    ('John_down'),
    ('Joe down'),
    ('Tom   Doe'),
    ('Tim down'),
    (null);

This query shows how to use patterns with wildcards (%) to find matches:

SELECT * 
  FROM like_example 
  WHERE subject LIKE ANY ('%Jo%oe%','T%e')
  ORDER BY subject;
+-------------+
| SUBJECT     |
|-------------|
| Joe   Doe   |
| John  Dddoe |
| Tom   Doe   |
+-------------+

This query shows how to use an escape character to indicate that a character that is usually a wild card (‘_’) should be treated as a literal.

SELECT * 
  FROM like_example 
  WHERE subject LIKE ANY ('%J%h%^_do%', 'T%^%e') ESCAPE '^'
  ORDER BY subject;
+-----------+
| SUBJECT   |
|-----------|
| John_down |
+-----------+