Categories:

String & Binary Functions (Matching/Comparison)

CONTAINS

Returns true if expr1 contains expr2. Both expressions must be text or binary expressions.

Tip

You can use the search optimization service to improve the performance of queries that call this function. For details, see Search Optimization Service.

Syntax

CONTAINS( <expr1> , <expr2> )
Copy

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:

  • pi (punctuation-insensitive).

  • cs-ai (case-sensitive, accent-insensitive).

Note

To use this function with a column that has the upper or lower collation specifiers, you must enable the 2024_02 behavior change bundle in your account.

To enable this bundle in your account, execute the following statement:

SELECT SYSTEM$ENABLE_BEHAVIOR_CHANGE_BUNDLE('2024_02');
Copy

Examples

SELECT * from strings;

---------+
    S    |
---------+
 coffee  |
 ice tea |
 latte   |
 tea     |
 [NULL]  |
---------+

SELECT * FROM strings WHERE CONTAINS(s, 'te');

---------+
    S    |
---------+
 ice tea |
 latte   |
 tea     |
---------+
Copy

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                               |
+------------------------------------+
Copy