Performs a case-sensitive comparison to match a string against all of one or more specified patterns. Use this function in a WHERE clause to filter for matches.
Tip
You can use the search optimization service to improve the performance of queries that call this function. For details, see Search Optimization Service.
<subject> LIKE ALL (<pattern1> [, <pattern2> ... ] ) [ ESCAPE <escape_char> ]
Copy
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 a BOOLEAN or NULL. The value is TRUE if there is a match. Otherwise, returns FALSE. Returns NULL if any argument is NULL.
Usage notes¶To include single quotes or other special characters in pattern matching, you can use a backslash escape sequence.
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.
You can use the NOT logical operator before the subject to perform a case-sensitive comparison that returns TRUE if it does not match any of the specified patterns.
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%).
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 ...)
Copy
Only the upper, lower, and trim collation specifications are supported. Combinations with upper, lower, and trim are also supported (for example, upper-trim and lower-trim), except for locale combinations (for example, en-upper).
Create a table that contains some strings:
CREATE OR REPLACE TABLE like_all_example(name VARCHAR(20));
INSERT INTO like_all_example VALUES
('John Dddoe'),
('Joe Doe'),
('John_do%wn'),
('Joe down'),
('Tom Doe'),
('Tim down'),
(null);
Copy
This query shows how to use patterns with wildcards (%) to find matches:
SELECT *
FROM like_all_example
WHERE name LIKE ALL ('%Jo%oe%','J%e')
ORDER BY name;
Copy
+-------------+ | NAME | |-------------| | Joe Doe | | John Dddoe | +-------------+
This query shows that all patterns need to match for a successful result:
SELECT *
FROM like_all_example
WHERE name LIKE ALL ('%Jo%oe%','J%n')
ORDER BY name;
Copy
+------+ | NAME | |------| +------+
This query shows how to use an escape character to indicate that characters that are usually wild cards (_ and %) should be treated as literals.
SELECT *
FROM like_all_example
WHERE name LIKE ALL ('%J%h%^_do%', 'J%^%wn') ESCAPE '^'
ORDER BY name;
Copy
+------------+ | NAME | |------------| | John_do%wn | +------------+
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4