Salesforce Docs Portal

CONTAINS / ICONTAINS

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Checks whether a string contains a specified substring or character value. CONTAINS performs a case-sensitive search, while ICONTAINS performs a case-insensitive search.

Syntax

contains(<string>, <substring>)
icontains(<string>, <substring>)

Arguments

Required

  • <string>: The string to search within.
  • <substring>: The substring or character to search for.

Returns

Returns a boolean value indicating whether the string contains the substring, or NULL if either argument is NULL.

Considerations

  • Empty substring: An empty substring returns true for any non-NULL string.
  • Literal matching: Treats the _ and % characters as literal characters, not wildcards.
  • Collation support: Use collations for locale-aware matching, including case-insensitive (_ci) and accent-insensitive (_ai) options.

Examples

Case-Sensitive Search

Check whether a string contains a substring (case-sensitive).

SELECT contains('Hello World', 'World') AS result;

Returns true.

SELECT contains('Hello World', 'world') AS result;

Returns false because the case doesn’t match.

Case-Insensitive Search

Check whether a string contains a substring (case-insensitive).

SELECT icontains('Hello World', 'world') AS result;

Returns true because icontains ignores case differences.

NULL Handling

Returns NULL when either argument is NULL.

SELECT contains('tableau', NULL) AS result;

Returns NULL.

SELECT contains(NULL, 'water') AS result;

Returns NULL.

Empty Substring

An empty substring is always contained in any string.

SELECT contains('tableau', '') AS result;

Returns true.

Literal Matching

Matches the _ and % characters literally, not as wildcards.

SELECT contains('Tableau', '_') AS result;

Returns false because it treats _ as a literal underscore.

SELECT contains('Tableau_', '_') AS result;

Returns true.

Using Collations

Use collations for locale-aware matching.

SELECT contains(name COLLATE "de_DE_ci", 'ABC') AS result
FROM products;

Performs a case-insensitive search using German locale rules.

SELECT contains(name COLLATE "en_US_ai", 'cafe') AS result
FROM products;

Performs an accent-insensitive search using the US English locale rules, matching both "cafe" and "café".

Related Documentation