When you start learning the QUERY function, one of the most important skills to master is string matching, or text filtering. Unlike numbers and dates, which use simple comparison operators, filtering text correctly requires more specialized string operators.

Whether you need an exact match, a partial match, or rows that start or end with specific text, choosing the right string-matching method makes your formulas easier to read, faster to calculate, and easier to maintain.

In this guide, you’ll learn all string-matching operators used within QUERY conditions in Google Sheets, when to use each one, and how to avoid common mistakes.

Scope note: This guide focuses on text-matching operators used in QUERY conditions.
It does not cover how conditions are combined or structured logically.
For AND / OR / NOT logic and condition structuring, see WHERE Clause in Google Sheets QUERY: Logical Conditions Explained.

What Is String Matching in Google Sheets QUERY?

String matching in Google Sheets QUERY refers to filtering rows based on text values using string-specific operators inside QUERY conditions.

For example, you may want to:

• Return only rows that contain the word “Apple”
• Exclude rows equal to “Apple”
• Match values that start with or end with specific text
• Use patterns or regular expressions for advanced logic

The best way to understand these options is through examples.

Use the following quick guide to choose the right string-matching operator:

Sample Data Used in This Tutorial

Use the following dataset for all examples:

Overview of String Matching Methods in QUERY

The table below summarizes all string-matching operators available in the QUERY WHERE clause and helps you quickly decide which one to use.

Note: All string-matching operators are case-sensitive by default.
Use LOWER() or UPPER() to perform case-insensitive matching.

Exact String Match

Use an exact match when the text must match precisely.

=QUERY(A1:C,"select * where B = 'Apple Juice'",1)

Case-Insensitive Exact String Match

=QUERY(A1:C,"select * where LOWER(B) = 'apple juice'",1)

Excluding an Exact Match

=QUERY(A1:C,"select * where B <> 'Apple Juice'",1)

These examples demonstrate exact string matching using simple comparison operators in Google Sheets QUERY.

Using CONTAINS for Substring Matching

CONTAINS is used when a column contains the specified text anywhere in the value.

=QUERY(A1:C,"select * where B contains 'Apple'",1)

Case-Insensitive CONTAINS Match

=QUERY(A1:C,"select * where LOWER(B) contains 'apple'",1)

Excluding a Substring Match

=QUERY(A1:C,"select * where NOT B contains 'Apple'",1)

Related guides

• CONTAINS Substring Match in Google Sheets QUERY
• Multiple CONTAINS in WHERE Clause in QUERY

STARTS WITH and ENDS WITH String Matching in QUERY

STARTS WITH is used for prefix matching, while ENDS WITH is used for suffix matching.

Starts With “Apple”

=QUERY(A1:C,"select * where B starts with 'Apple'",1)

Ends With “Juice”

=QUERY(A1:C,"select * where B ends with 'Juice'",1)

You can combine these with:

• LOWER() or UPPER() for case-insensitivity
• NOT to exclude matches

Related guides

• STARTS WITH and NOT STARTS WITH in QUERY
• ENDS WITH and NOT ENDS WITH in QUERY

Using the LIKE Operator for Pattern Matching

LIKE supports simple wildcard matching and offers more control than CONTAINS.

Wildcards:

• % → zero or more characters
• _ → exactly one character

LIKE Operator Example With Wildcards

To match descriptions like:

• Fresh apple drink
• Fresh banana drink
• Fresh mango drink

Use:

=QUERY(A1:C,"select * where C like 'Fresh % drink'",1)

Related guides

• How to Use the LIKE Operator in Google Sheets QUERY

REGEX Matching With MATCHES

MATCHES is the most powerful string-matching operator in Google Sheets QUERY. It supports regular expressions and, in many cases, can replace all other string-matching methods.

Common Equivalents Using MATCHES

All the previous string-matching examples can be rewritten using MATCHES, as shown below:

=QUERY(A1:C,"select * where B matches 'Apple Juice'",1)      // exact match
=QUERY(A1:C,"select * where B matches '.*Apple.*'",1)        // contains
=QUERY(A1:C,"select * where B matches 'Apple.*'",1)          // starts with
=QUERY(A1:C,"select * where B matches '.*Juice'",1)          // ends with
=QUERY(A1:C,"select * where C matches 'Fresh .* drink'",1)   // LIKE equivalent

Although MATCHES is flexible, it is not always the best choice. In many cases, simpler operators such as CONTAINS, STARTS WITH, or ENDS WITH are easier to read and maintain.

Starts With Apple or Banana

=QUERY(A1:C,"select * where B matches '(Apple.*|Banana.*)'",1)

This returns rows where the text in column B starts with Apple or Banana.

Related guides

• MATCHES Regular Expression in Google Sheets QUERY

When MATCHES is used to test a value against a list of allowed values (for example, as an alternative to SQL’s IN operator), the focus shifts from text pattern matching to logical condition handling.

In such cases, MATCHES is being used for condition logic, not for text pattern selection.

Choosing the Right String Matching Method

• I need partial text → CONTAINS
• Text begins with… → STARTS WITH
• Text ends with… → ENDS WITH
• Simple patterns → LIKE
• Advanced logic → MATCHES

Common Mistakes With String Matching in QUERY

• Using string operators on numeric or date columns
• Forgetting that QUERY string matching is case-sensitive by default
• Using MATCHES when a simpler operator would work

Tip: Use the simplest operator that solves the problem. MATCHES is powerful but can be harder to read and slightly heavier to calculate, so use it only when simpler operators are not sufficient.

Conclusion

Mastering string matching in Google Sheets QUERY allows you to build cleaner, faster, and more reliable formulas. By choosing the correct operator for each scenario, you avoid unnecessary complexity while keeping your queries easy to understand.

Related QUERY Guides

• How to Filter Numbers Only from a Mixed Data Column