In Snowflake, use CONTAINS(string, substring) to check whether one string includes another; it returns TRUE or FALSE, or NULL if an argument is NULL. For example, CONTAINS('blueberry', 'berry') returns TRUE. Use ILIKE when you need a case-insensitive pattern match, or a regular expression when the match needs more than a literal substring.
How CONTAINS works
CONTAINS searches the first expression for the second expression as a continuous sequence of characters. Its argument order is important: the string being searched comes first, and the substring comes second. The function does not treat the second argument as a pattern, so characters such as percent signs are searched literally.
SELECT CONTAINS('Snowflake SQL', 'flake') AS found;
This query returns TRUE. Reversing the arguments searches the shorter text for the longer one, so CONTAINS('flake', 'Snowflake SQL') returns FALSE. You can use a column in either position where the data types are compatible. For instance, if description contains product descriptions, CONTAINS(description, 'wireless') checks each row for that exact sequence.
As a predicate in a query, the function can filter rows directly:
SELECT product_id, description
FROM products
WHERE CONTAINS(description, 'wireless');
The comparison is case-sensitive by default: CONTAINS('Snowflake', 'snow') returns FALSE because the letter case differs. It also looks for a substring, not a whole word. Searching for 'art' can match 'carton' as well as the standalone word 'art'. That distinction matters when searching names, notes, descriptions, or other free-form text.
Choosing between CONTAINS, LIKE, and ILIKE
These options all help find text, but they express different intentions. Choose the one that makes the desired match easiest to understand and least likely to produce accidental matches:
CONTAINS(text, 'abc')checks for the literal substringabc. It is a natural choice for a straightforward substring check.text LIKE '%abc%'uses a pattern. A percent sign matches any sequence of characters, while an underscore matches one character.text ILIKE '%abc%'uses a pattern without distinguishing uppercase and lowercase letters.REGEXP_LIKE(text, pattern)is useful for structured rules, such as matching a group of alternatives or a particular character arrangement.
For a simple literal search, CONTAINS avoids having to add percent signs around the term. By contrast, LIKE and ILIKE are useful when the wildcard behavior itself is wanted. For example, email ILIKE '%@example.com' can find addresses that end with that domain, without distinguishing the case of the text.
Wildcards are not literal characters in a LIKE pattern. If the search term comes from user input and might contain % or _, decide whether those characters should act as wildcards or as ordinary text. Use the LIKE predicate’s escape option when you need to treat a wildcard character literally, and test the resulting pattern with representative inputs. With CONTAINS, percent and underscore are ordinary characters in the search string.
Making a search case-insensitive
If case should not matter, one option is ILIKE with percent signs around the search term. For example, description ILIKE '%snowflake%' matches occurrences with differing letter case. This is concise for a fixed pattern, but remember that LIKE wildcards are active in the pattern.
Another option for a literal substring is to normalize both inputs with LOWER before calling CONTAINS. With a column named description and the search value in a column named term, the predicate is:
CONTAINS(LOWER(description), LOWER(term))
Normalizing both sides is essential: lowering only the description would still leave an uppercase search term unmatched. This approach is also helpful when the search value is stored in a table rather than written directly into the SQL statement. It remains a substring search, not a whole-word search. For language-specific case rules or unusual Unicode text, confirm that the chosen normalization matches the application’s requirements rather than assuming every language has identical case behavior.
Using regular expressions for more complex matches
Use a regular expression when the requirement is more specific than “these characters occur in order.” For example, an expression can allow a choice among alternatives or constrain which characters appear around a match. Snowflake provides REGEXP_LIKE for testing a string against a regular expression. Unlike a bare substring check, regular-expression syntax has special characters that need to be escaped when they should be treated literally.
Snowflake implicitly anchors the pattern used by REGEXP_LIKE to the beginning and end of the subject. To express a contains-style regex match, a pattern commonly includes .* before and after the desired expression, such as '.*berry.*' for a simple occurrence of “berry.” The dot and repetition symbols have regex meanings, so this is not a drop-in replacement for arbitrary user-entered search text. For simple searches, prefer CONTAINS or ILIKE; regular expressions are more expressive but can be harder to read and maintain.
NULL values, empty terms, and data types
SQL uses three-valued logic: a condition can be TRUE, FALSE, or NULL (unknown). If either input to CONTAINS is NULL, the result is NULL rather than FALSE. In a WHERE clause, only rows for which the condition is TRUE are retained, so a row with a NULL description will not appear in the results. If the application explicitly wants to treat unknown as “not found,” use COALESCE:
WHERE COALESCE(CONTAINS(description, 'urgent'), FALSE)
That expression changes a NULL result to FALSE; it does not tell you whether the source value was missing. If missing descriptions need to be reported separately, check for NULL explicitly with description IS NULL.
Check the types of both expressions when a query raises an argument-type error or produces an unexpected conversion. A semi-structured value in a VARIANT column is not always the text field you intend to search. Extract the relevant scalar and cast it to a string when appropriate, for example payload:customer_name::STRING. Searching an entire serialized object can match text in unrelated keys or values and may not express the intended data rule. An empty search term is another edge case worth validating in the application; reject it or handle it explicitly if an empty term should not match every row.
Searching rows and semi-structured data
For a table column, put the predicate in WHERE to return matching rows, or in a SELECT expression to show a Boolean result alongside the data. The latter is useful for checking logic before using it as a filter:
SELECT description,
CONTAINS(description, 'delay') AS mentions_delay
FROM support_tickets;
If a row can contain several text fields, combine predicates with OR when a match in any field should qualify, or with AND when all specified fields must match. Add parentheses when mixing these operators so the intended grouping is clear. For example, a search for a term in either a title or a summary can be written as CONTAINS(title, 'delay') OR CONTAINS(summary, 'delay').
For JSON-like data, first decide whether you need to search one extracted value, a set of array elements, or a serialized representation. Extracting a known property is usually clearer and avoids matches elsewhere in the document. If the relevant value is inside an array, the query may need to expand or otherwise inspect the array elements rather than convert the whole object to text. The right approach depends on the schema and whether the requirement is a literal fragment, a specific property value, or a match in any array item.
Common mistakes and query considerations
A frequent mistake is expecting a word search when the function performs substring matching. If searching for 'art' must not match 'carton', define a boundary-aware rule instead of relying on CONTAINS. Depending on the text format, that may mean tokenizing the text or writing a carefully tested regular expression. Punctuation, hyphens, and language-specific word boundaries can make a universal “whole word” rule less simple than it first appears.
Another common error is reversing the arguments or assuming that CONTAINS accepts wildcard patterns. Remember that the searched value is first and the literal text is second. Use LIKE or ILIKE for wildcard patterns, and use explicit case normalization or a case-insensitive pattern when appropriate. Also check for NULLs before concluding that every nonmatching row returned FALSE.
Substring predicates can require examining substantial text, especially when applied across a large table. Do not assume that a CONTAINS filter has the same pruning behavior as an equality condition. Select only needed columns, apply other selective filters where appropriate, and inspect the query profile to understand the work performed. Snowflake’s search optimization service can help with certain search workloads when configured for suitable columns, but whether it helps depends on the predicate, data, configuration, and workload. Review current Snowflake documentation and cost implications before enabling it; measure the actual query rather than assuming an optimization will improve every contains search.
Frequently Asked Questions
Is Snowflake CONTAINS case-sensitive?
Yes, the ordinary CONTAINS comparison is case-sensitive. For a case-insensitive pattern, use ILIKE; for a literal substring, you can apply LOWER to both the text and search term before calling CONTAINS.
What does CONTAINS return when the value is not found?
It returns FALSE when both inputs are non-NULL and the substring does not occur in the searched string. If either argument is NULL, the result is NULL instead.
Can I use wildcards with Snowflake CONTAINS?
No. CONTAINS searches for its second argument literally, so percent and underscore do not act as wildcards. Use LIKE or ILIKE when you need the percent or underscore pattern operators.
How do I find a substring without regard to case?
Use ILIKE '%term%' for a pattern search, or compare LOWER of both values with CONTAINS for a literal substring. If the term is dynamic, consider whether wildcard characters in it need special handling when using ILIKE.
Does CONTAINS find a whole word only?
No. It finds a sequence of characters anywhere in the string, including inside a longer word. Use a boundary-aware method, such as a tested regular expression or tokenization, if the requirement is to match complete words.
Related
- String Length in C++: size(), length(), and Examples
- Reverse a String in C++: std::reverse and Examples
- JavaScript split() Method: Syntax, Examples, and Tips
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Convert Percentage Text to NumberYes. If A2 contains the text 25%, enter =VALUE(A2) in another cell to convert it to a real…
- Excel Set Print Area to Fit on One Page: Steps and TipsTo set an Excel print area and fit it on one page, select the cells you want, choose…
- Can You Custom Freeze Panes in Excel? Here’s HowYes. In Excel for Microsoft 365 on Windows, select the cell immediately below the rows and to the…
- How to Add a Drop-Down in Excel Using Keyboard ShortcutsTo add a drop-down in Excel for Microsoft 365 on Windows, select the cell or cells, press Alt+D,…
- How to Insert a Page Break in Word for Windows and MacTo start a new page in Word, place the cursor where the next page should begin and press…
- Copilot Not Responding in Excel? Fixes to TryIf Copilot is not responding in Excel, first confirm that you are signed in with an account and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.