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.

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

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

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

• 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.

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

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

Returns true.

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

Returns false because the case doesn’t match.

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

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

Returns true because icontains ignores case differences.

Returns NULL when either argument is NULL.

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

Returns NULL.

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

Returns NULL.

An empty substring is always contained in any string.

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

Returns true.

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

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

Returns false because it treats _ as a literal underscore.

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

Returns true.

Use collations for locale-aware matching.

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

Performs a case-insensitive search using German locale rules.

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

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

• POSITION
• STRPOS
• String Types