String & Binary Functions (Matching/Comparison)
Allows case-sensitive matching of strings based on comparison with one or more patterns.
The operation is similar to
LIKE. If and only if the input string matches all of the patterns, this returns the input string.
- See also:
<subject> LIKE ALL (<pattern1> [, <pattern2> ... ] ) [ ESCAPE <escape_char> ]
The string to compare to the pattern(s).
The pattern(s) that the string is to be compared to. You must specify at least one pattern.
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.
The data type of the returned value is VARCHAR.
SQL wildcards are supported in
An underscore (
_) matches any single character.
A percent sign (
%) matches any sequence of zero or more characters.
patterninclude newline characters (
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.
If the function is used with a subquery, the subquery should return a single row.
For example, the following should be used only if the subquery returns a single row:
SELECT ... WHERE x LIKE ALL (SELECT ...)
Create a table that contains some strings:
CREATE OR REPLACE TABLE like_all_example(subject varchar(20)); INSERT INTO like_all_example VALUES ('John Dddoe'), ('Joe Doe'), ('John_do%wn'), ('Joe down'), ('Tom Doe'), ('Tim down'), (null);
This query shows how to use patterns with wildcards (%) to find matches:
SELECT * FROM like_all_example WHERE subject LIKE ALL ('%Jo%oe%','J%e') ORDER BY subject; +-------------+ | SUBJECT | |-------------| | Joe Doe | | John Dddoe | +-------------+
This query shows how all patterns need to match for successful result:
SELECT * FROM like_all_example WHERE subject LIKE ALL ('%Jo%oe%','J%n') ORDER BY subject; +---------+ | SUBJECT | |---------| +---------+
This query shows how to use an escape character to indicate that characters that are usually wild cards (‘_’) , (‘%’) should be treated as literals.
SELECT * FROM like_all_example WHERE subject LIKE ALL ('%J%h%^_do%', 'J%^%wn') ESCAPE '^' ORDER BY subject; +------------+ | SUBJECT | |------------| | John_do%wn | +------------+