Performs a case-sensitive comparison to match a string against any of one or more specified patterns. Use this function in a WHERE clause to filter for matches. For case-insensitive matching, use ILIKE ANY instead.
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 ANY (<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 ANY (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_example(name VARCHAR(20));
INSERT INTO like_example VALUES
('John Dddoe'),
('Joe Doe'),
('John_down'),
('Joe down'),
('Tom Doe'),
('Tim down'),
(null);
Copy
This query shows how to use patterns with wildcards (%) to find matches:
SELECT *
FROM like_example
WHERE name LIKE ANY ('%Jo%oe%','T%e')
ORDER BY name;
Copy
+-------------+ | NAME | |-------------| | 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 name LIKE ANY ('%J%h%^_do%', 'T%^%e') ESCAPE '^'
ORDER BY name;
Copy
+-----------+ | NAME | |-----------| | John_down | +-----------+
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