- 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:
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
) insubject
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 |
+-----------+