Return true when the input string contains the given substring, false otherwise.
CONTAINS(str, substr)## Overview Returns true when the input string contains the given substring at any position, and false otherwise. CONTAINS is a boolean-returning alternative to POSITION/STRPOS/INSTR that reads more naturally in WHERE clauses. The match is literal and case-sensitive; for pattern matching use LIKE or REGEXP_LIKE. CONTAINS is semantically equivalent to STRPOS(str, substr) <> 0 but is usually more readable. ## Behavior - Returns NULL when either argument is NULL. - Returns true for an empty substring: the empty string is considered contained in every string. - Returns false when the substring is not found at any position. - The search is case-sensitive and literal; special characters such as '.', '*', and '%' are treated as ordinary characters. - Operates on Unicode code points; multi-byte UTF-8 characters are matched correctly. - Short-circuits as soon as the first occurrence is found. ## Compatibility - Widely supported under the CONTAINS name with matching semantics. - Equivalent predicates: substr IS NOT NULL AND POSITION(substr IN str) > 0, and substr IS NOT NULL AND str LIKE '%' || substr || '%' (which is case-sensitive but requires escaping SQL wildcards).
| Name | Type | Description |
|---|---|---|
str | Specifies the string to search within. | |
substr | Specifies the substring to search for. The match is case-sensitive and literal (not a pattern). |
-- Substring present
SELECT CONTAINS('hello world', 'world'); -- true-- Substring absent
SELECT CONTAINS('hello world', 'xyz'); -- false-- Case-sensitive
SELECT CONTAINS('Hello', 'hello'); -- false-- Empty substring is considered present in every string
SELECT CONTAINS('hello', ''); -- true-- NULL propagates
SELECT CONTAINS(CAST(NULL AS VARCHAR), 'x'); -- NULL-- Filter a column for rows containing a marker
SELECT order_id
FROM retail.sales.orders
WHERE CONTAINS(notes, 'REFUND');