- Categories:
String & Binary Functions (Matching/Comparison)
CONTAINS¶
Returns true if expr1
contains expr2
. Both expressions must be text or binary expressions.
Syntax¶
CONTAINS( <expr1> , <expr2> )
Arguments¶
expr1
The string to search in.
expr2
The string to search for.
Returns¶
Returns a BOOLEAN
. The value is True if expr2
is found inside expr1
. Returns NULL if either
input expression is NULL. Otherwise, returns False.
Collation Details¶
The collation specifications of all input arguments must be compatible.
This function does not support the following collation specifications:
lower
.upper
.pi
(punctuation-insensitive).cs-ai
(case-sensitive, accent-insensitive).
Examples¶
SELECT * from strings;
---------+
S |
---------+
coffee |
ice tea |
latte |
tea |
[NULL] |
---------+
SELECT * FROM strings WHERE CONTAINS(s, 'te');
---------+
S |
---------+
ice tea |
latte |
tea |
---------+
The following example uses CONTAINS
with collation:
-- Should return True. SELECT CONTAINS(COLLATE('ñn', 'sp'), COLLATE('n', 'sp')); +---------------------------------------------------+ | CONTAINS(COLLATE('ÑN', 'SP'), COLLATE('N', 'SP')) | |---------------------------------------------------| | True | +---------------------------------------------------+ SELECT CONTAINS(COLLATE('ñn', 'sp'), 'n'); +------------------------------------+ | CONTAINS(COLLATE('ÑN', 'SP'), 'N') | |------------------------------------| | True | +------------------------------------+