Databricks SQL CONTAINS: Find Text in a String

How-to basics · Updated November 22, 2026

In Databricks SQL, contains(expr, subExpr) returns true when the string expr includes subExpr as a substring, and false when it does not. For example, contains('Databricks SQL', 'SQL') returns true; use it in a WHERE clause to keep rows whose text includes a chosen phrase.

What CONTAINS checks

contains looks for an exact sequence of characters within a value. It does not interpret the search text as a word, phrase pattern, or SQL wildcard. In contains('shipping delayed', 'delayed'), the second argument is found inside the first, so the result is true. But contains('shipping delayed', 'delay*') looks for the literal characters delay*; the asterisk is not a wildcard for this function.

The function answers a yes-or-no question, so its result is a Boolean rather than a position or a copy of the matching text. The two arguments should be compatible values of the same type: string expressions for searching text, or binary expressions for searching bytes. For ordinary text work, pass string columns and string literals. Databricks SQL can resolve some compatible expressions through normal type rules, but explicit, matching types make queries easier to understand and avoid unexpected coercion.

A useful mental model is “does this value contain these characters in this order?” The match can appear at the beginning, in the middle, or at the end. It need not occupy the whole value, and the function does not require the match to start or end at a word boundary.

Basic syntax and examples

The syntax is contains(expr, subExpr). Put the text to search in the first argument and the substring to find in the second. Here is a simple query that returns a Boolean column:

SELECT contains('Databricks SQL', 'SQL') AS has_sql;

The result of this expression is true. Reversing the arguments asks a different question: contains('SQL', 'Databricks SQL') is false because the longer phrase is not inside the shorter string.

To filter a table, supply the string column as the first argument and the search phrase as the second:

SELECT order_id, customer_note
FROM orders
WHERE contains(customer_note, 'expedite');

This query returns rows where customer_note contains the exact substring expedite. It does not return an extra match for Expedite merely because the first letter is capitalized under the usual case-sensitive behavior of a binary-collated string. If the phrase is stored in a column, the second argument can be a column too, provided the types are compatible.

You can also put the function in a projection to inspect matches without filtering any records:

SELECT customer_note,
       contains(customer_note, 'expedite') AS mentions_expedite
FROM orders;

This is handy for validating a condition before using it to exclude rows, or for producing a flag that can be grouped or counted elsewhere in a query.

Case, punctuation, and exact matching

Do not assume that contains ignores letter case. With the standard binary string collation, uppercase and lowercase letters are distinct: a search for 'sql' does not match 'SQL'. Databricks supports collations in some contexts, and comparison behavior can depend on the collation associated with the string expression. If the query relies on case-insensitive matching, check the collation and behavior of the actual expressions rather than relying on an unstated assumption.

A portable way to make a basic case-insensitive search explicit is to normalize both sides with lower:

WHERE contains(lower(customer_note), lower('Expedite'))

That expression compares lowercase forms, so EXPEDITE, Expedite, and expedite match in ordinary English text. Case conversion is not the same as every possible form of Unicode-aware case folding, however. For multilingual data or specialized collation requirements, use a suitable collation or verify the behavior with representative values.

Punctuation and spaces are also literal. contains('ticket #4821 open', '#4821') is true, while searching for 'ticket#4821' is false because the space differs. A trailing space in the search value matters as well. If data comes from forms, logs, or imported files, trimming or normalizing the source may be necessary—but do so only when spaces and punctuation are not meaningful to the application.

NULL values and empty substrings

Like many SQL expressions, contains follows three-valued logic. If either argument is NULL, the result is NULL, not false. For example, if customer_note is null, contains(customer_note, 'refund') evaluates to null because SQL cannot determine whether missing text contains the phrase.

This has a practical effect in a WHERE clause: only rows for which the condition is true are kept. Rows where it is false or null are not returned. Likewise, NOT contains(customer_note, 'refund') does not automatically include null notes; negating null still produces null. If the intended rule is to treat a missing note as “does not contain refund,” make that choice explicit:

WHERE NOT coalesce(contains(customer_note, 'refund'), false)

Here, coalesce substitutes false when the contains result is null, and the negation therefore includes rows with missing notes. Choose the null policy to match the meaning of the data; treating unknown as absent is not always appropriate.

An empty search string is a special but predictable case: it is contained in any string, including an empty string. That means contains(description, '') is true for non-null descriptions. If a search value comes from a user input, consider checking that it is not empty before applying the condition, or a blank search can match every non-null row.

Using CONTAINS in filters and reports

For a single fixed phrase, a direct predicate is usually clear. For example, an analyst might label support tickets that mention a product area, then count the matching rows by category. The same Boolean expression can be used in a SELECT, WHERE, or conditional expression. Ensure the chosen column is actually textual; searching a numeric identifier after converting it to text may find character sequences that are not meaningful matches.

When the terms to search vary by row, use a column for the substring rather than constructing SQL text dynamically. For example, contains(message, blocked_term) compares each message with the corresponding term. This is useful for row-by-row checks, but it does not mean “match any term in a list.” For a list of alternatives, combine Boolean tests or use a join against a terms table with a clearly defined matching strategy.

  • Use a literal in the second argument for a fixed phrase, such as contains(message, 'timeout').
  • Use a column in the second argument when each row supplies its own search term.
  • Use coalesce only when you have deliberately decided how null text should behave.
  • Test representative records, including capitalization, punctuation, empty values, and nulls, before relying on a filter for reporting.

A substring match is not the same as a word match. Searching for 'art' can match 'party' as well as 'art department'. If the business rule means a whole word, define what counts as a word boundary—especially when text can contain punctuation, hyphens, or multiple languages—and consider a regular-expression approach instead.

CONTAINS compared with LIKE, INSTR, and regular expressions

Use contains when the question is simply whether one literal substring appears in another string. It reads directly and returns a Boolean. For a basic substring test, instr(description, 'urgent') > 0 is another option: instr returns the one-based position of the substring when found and zero when it is absent. Choose it when the position itself is useful or when maintaining a query that already uses instr.

LIKE is useful when the pattern needs SQL wildcards. The percent sign matches a sequence of characters and the underscore matches a single character, so description LIKE '%urgent%' expresses a contains-style pattern. Those wildcard characters have pattern meaning in LIKE, unlike in contains. If a search must treat a wildcard character literally, escaping and the exact pattern rules matter; a literal-substring function can be less error-prone.

Regular expressions are appropriate for more complex rules, such as alternatives, character classes, or word boundaries. Databricks SQL provides regular-expression matching functions and operators, but regular expressions have their own syntax and escaping rules. They can be harder for another reader to review than a simple contains call. Prefer the simplest operator that represents the actual requirement, and test patterns against both positive and negative examples.

None of these choices changes the underlying question into full-text search. A substring predicate does not rank results by relevance, understand synonyms, or automatically split prose into meaningful words. For richer search requirements, use a search-oriented design rather than trying to build relevance behavior from many substring checks.

Performance and query design

contains is straightforward, but a query may need to examine text values to decide whether each row matches. Do not assume that arbitrary substring searching behaves like a lookup through a conventional text index. Databricks query performance depends on the data, file layout, filters, and execution plan; a condition that searches anywhere inside a string can be less selective than an equality condition on a well-organized key.

For large tables, reduce the candidate rows with other useful predicates where possible—for example, a date range or a category filter—before relying on a broad text search. Select only the columns needed by the query, and avoid repeating expensive text transformations in multiple places when a clearer query structure can calculate the condition once. Then inspect the query profile or execution details on the actual workload rather than assuming a particular optimization will apply.

Also consider whether the data model fits the task. If users repeatedly search structured codes, extracting those codes into a dedicated column can make the intent clearer and enable more appropriate filtering. If users need arbitrary phrase search across large volumes of prose, define the required search semantics and evaluate a search system designed for that workload. A single contains predicate is best suited to literal substring checks, not every kind of search.

Common mistakes to avoid

  • Reversing the arguments: the value being searched goes first; the substring goes second.
  • Expecting wildcard behavior: * and % are literal characters to contains. Use LIKE when SQL wildcards are intended.
  • Overlooking case: do not assume lowercase and uppercase text match; normalize or use an appropriate collation when needed.
  • Ignoring nulls: a null input yields null, and a null filter condition does not pass a WHERE clause.
  • Confusing substring with whole-word matching: a short search term can occur inside a larger word.
  • Passing the wrong data type: keep both arguments compatible and use strings for text rather than relying on implicit conversion.

When a result surprises you, reduce the condition to a small query that displays the original value alongside the Boolean result. That makes hidden whitespace, punctuation, nulls, and case differences easier to spot than inspecting only the filtered output.

Frequently Asked Questions

Is Databricks SQL CONTAINS case-sensitive?
With the usual binary string collation, matching is case-sensitive, so 'SQL' and 'sql' are different. Collation can affect string behavior, so verify the collation of your expressions if the query depends on case-insensitive matching.

Does CONTAINS support wildcards?
No. It searches for the literal substring supplied as its second argument. Use LIKE for percent and underscore wildcards, or a regular expression for a more complex pattern.

What happens when the string is NULL?
If either argument is null, the result is null. In a WHERE clause that row is not selected unless the query explicitly handles the null result, for example with coalesce.

How do I find rows that do not contain a phrase?
Use WHERE NOT contains(column_name, 'phrase') for non-null values. Decide separately whether null rows should be included; if so, coalesce the contains result to false before negating it.

Should I use CONTAINS or INSTR?
Use contains for a direct true-or-false test. Use instr when the character position matters or when its numeric result is convenient; it returns zero when the substring is absent and a one-based position when found.

Does CONTAINS find a whole word?
No. It finds characters anywhere inside the string, including inside a longer word. For whole-word matching, specify appropriate boundaries with a regular expression or another method that reflects the text and language rules in your data.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.