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 work with the following collation specifications:

  • lower
  • upper
  • 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                               |
+------------------------------------+