I have published dozens of tutorials related to Google Sheets Query, but I haven’t covered partial match, also known as substring match, in detail. This post is dedicated entirely to the CONTAINS Substring Match in Google Sheets Query.

This tutorial is all about matching part of a text within a whole string, where the condition evaluates to TRUE if the substring appears anywhere in the text.

Introduction

Many functions in Google Sheets support wildcards, but wildcard usage in the QUERY function works differently. Instead of traditional wildcards, QUERY provides several string comparison operators to perform text-based filtering.

Apart from basic comparison operators like =, <>, or !=, the QUERY function also supports additional string comparison operators. Their usage is straightforward once you understand the syntax.

Among the available string comparison operators, CONTAINS is commonly used for partial match in QUERY. This example is part of the broader String Matching in Google Sheets QUERY guide, which explains all text-matching operators used in QUERY.

Let’s see how to use the CONTAINS substring match in Google Sheets Query with simple examples.

How to Use the CONTAINS Substring Match in Google Sheets Query

Assume the sample data contains a list of fruit names in column A.

This is intentionally simple data so you can easily recreate it.

Example 1: Partial Match Using CONTAINS

Suppose column A includes values like:

• Mango (Neelam)
• Mango (Alphonso)
• Apple

To return all rows that contain “Mango” anywhere in the text, use:

=query(A1:B,"select A where A contains 'Mango'")

This formula returns only the rows that partially match “Mango”.

Example 2: Another Partial Match in QUERY

Now assume column A contains text such as:

• tech blog from India
• food magazine from India
• travel magazine

To return rows containing “magazine”, use:

=query(A1:B,"select A where A contains 'magazine'")

This returns all rows where the substring magazine appears anywhere in column A.

Things to Know About CONTAINS Substring Match

1. CONTAINS in QUERY WHERE Clause for Partial Match

The CONTAINS substring match checks whether a substring exists anywhere in the text:

• Beginning
• Middle
• End

Example:

=query(A1:A,"select A where A contains 'blog'")

This returns all rows that contain the word blog, regardless of its position.

2. Case-Insensitive CONTAINS in Google Sheets Query

By default, CONTAINS in QUERY is case-sensitive.

That means:

=query(A1:A,"select A where A contains 'Magazine'")

will not match text containing magazine in lowercase.

To perform a case-insensitive partial match in QUERY, use the UPPER or LOWER scalar functions.

Using UPPER

=query(A1:A,"select A where UPPER(A) contains 'MAGAZINE'")

Using LOWER

=query(A1:A,"select A where LOWER(A) contains 'magazine'")

Both formulas return the same result, regardless of text case.

How to Use DOES NOT CONTAIN in QUERY

To return rows that do not contain a specific substring, use the NOT logical operator with CONTAINS.

Example:

=query(A1:A,"select A where NOT A contains 'blog'")

This formula returns rows where the text does not include “blog”.

Final Notes

You may also want to place the substring criterion in a cell reference instead of hardcoding it in the formula. Since that topic is already covered in detail, I’ve skipped it here.

Refer to this guide instead:
How to Use Cell Reference in Google Sheets Query

Resources

• Multiple CONTAINS in WHERE Clause in Google Sheets Query
• Literals in Google Sheets QUERY – How to Use Them
• How to Use AND, OR, and NOT in Google Sheets Query