LIKE ANY¶

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 % (e.g. %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 |
+-----------+