How to Use the LIKE Operator in Google Sheets QUERY

The LIKE operator in Google Sheets QUERY is used to match text patterns. You can use it in the WHERE clause to perform flexible text searches.

LIKE supports two wildcard characters:

% (percentage)
_ (underscore)

These wildcards are similar to the asterisk (*) and question mark (?) wildcards used in many other Google Sheets functions.

This tutorial is part of the String Matching in Google Sheets QUERY guide, which covers all text-matching methods available in QUERY.

Table of Contents

When to Use the LIKE Operator in Google Sheets QUERY

Use LIKE when you want to…	Pattern
Find text that starts with A	`A%`
Find text that ends with land	`%land`
Find text that contains “oo”	`%oo%`
Match exactly one character	`A_`

The following sections demonstrate these wildcard patterns with practical examples.

Syntax

WHERE column LIKE 'pattern'

Where:

column – The column to search.
pattern – The text pattern to match. It can include the % and _ wildcards.

Note
The LIKE operator is case-sensitive. To perform a case-insensitive search, wrap the column in LOWER() or UPPER() and use a matching lowercase or uppercase pattern.

Using the LIKE Operator in Google Sheets QUERY

Let’s look at a few examples to understand how to use the LIKE operator in Google Sheets QUERY.

Sample Data

We’ll use a list of place names (countries and cities) in column A for most of the examples below.

Using the LIKE operator in Google Sheets QUERY to match country names

Using the `%` (Percentage) Wildcard

Alternative to the asterisk (*) wildcard

The % wildcard matches zero or more characters.

Match All Text Values

=QUERY(A2:A, "select A where A like '%'")

This formula returns all text values in column A because % matches any sequence of characters.

Match Text That Starts with “A”

=QUERY(A2:A, "select A where A like 'A%'")

Result:

Australia
Austria
Auckland

This returns all text values that start with the letter A.

Match Text That Ends with “land”

=QUERY(A2:A, "select A where A like '%land'")

Result:

Oakland
Switzerland
Auckland

This returns all text values that end with “land”.

Match Text That Starts with “Sl” and Ends with “ia”

=QUERY(A2:A, "select A where A like 'Sl%ia'")

Result:

Slovakia
Slovenia

This returns all text values that start with “Sl”, end with “ia”, and contain any number of characters in between.

Using the `_` (Underscore) Wildcard

Alternative to the question mark (?) wildcard

The _ wildcard matches exactly one character.

The following examples use a different sample dataset.

Match Three-Character Text Starting with “Am”

=QUERY(A2:A, "select A where A like 'Am_'")

Result:

Ami
Amy

This returns text values with exactly one character after “Am”.

Match Three-Character Text Starting with “A”

=QUERY(A2:A, "select A where A like 'A__'")

Result:

Ann
Ash

This returns all text values with exactly three characters that start with “A”.

Case-Insensitive LIKE Match in Google Sheets QUERY

To perform a case-insensitive search, combine LIKE with the LOWER() or UPPER() scalar function.

Example

=QUERY(A2:A, "select A where lower(A) like 'a__'")

Since LOWER(A) converts all values to lowercase, the search pattern must also be lowercase.

This formula returns matching values regardless of the text case.

Using Cell References with LIKE in QUERY

Instead of hardcoding the pattern in the formula, you can store it in a cell and reference that cell in the LIKE condition.

Example

=QUERY(A2:A, "select A where A like '"&D1&"'")

Here, cell D1 contains the pattern:

Sl%ia

The formula returns all matching text values based on the pattern in cell D1.

For a detailed explanation, see How to Use Cell References in Google Sheets QUERY.

NOT LIKE in Google Sheets QUERY

Use NOT with LIKE to return values that do not match a pattern.

Exclude Text That Starts with “A”

=QUERY(A2:A, "select A where not A like 'A%'")

This returns all text values that do not start with “A”.

Exclude Three-Character Text Starting with “Am”

=QUERY(A2:A, "select A where not A like 'Am_'")

This excludes text values with exactly three characters that start with “Am”, such as Ami, but returns longer values such as Ami Santo.

FAQ

Is LIKE case-sensitive?

Yes. The LIKE operator is case-sensitive in Google Sheets QUERY. To perform a case-insensitive search, use the LOWER() or UPPER() scalar function.

Does LIKE support the `*` wildcard?

No. The LIKE operator supports only the % and _ wildcards.

Can I use cell references with LIKE?

Yes. Store the search pattern in a cell and reference that cell in the LIKE condition. See the “Using Cell References with LIKE in QUERY” section above.

Conclusion

The LIKE operator in Google Sheets QUERY provides a flexible way to filter text using wildcard patterns. Whether you need to match prefixes, suffixes, partial text, or single-character patterns, LIKE provides a simple and flexible solution. For case-insensitive searches, combine it with the LOWER() or UPPER() scalar function.

16 COMMENTS

Melissa Watson November 9, 2022 At 2:10 am

Hey guys. I’m new to SQL and have learned some basics.

I have a master spreadsheet that I’m pulling data from and placing it into several other worksheets based on someone’s last name.

=query (Failures,"Select* Where C like 'L%' or C like 'M%' or C like 'N%' or C like 'O%' or C like 'P%' or C like 'Q%'",1)

Can someone help me figure out how to exclude the data where “none” appears while keeping data where last names start with the letter “N”?

I think it’s my syntax, but I’m not sure what I’m doing wrong. Thank you!!!

- Prashanth November 9, 2022 At 1:58 pm
  
  Hi, Melissa Watson,
  
  You can try these modifications.
  1. Replaces multiple LIKE operators with a single MATCH operator.
  2. Use AND to exclude the text “None.”
  
  Example Formula:
  
  =query (A1:C,"Select* Where C matches 'L.*|M.*|N.*|O.*|P.*|Q.*' and not(lower(C))='none'",1)
  
  - Melissa Watson November 9, 2022 At 8:08 pm
    
    Worked beautifully. Thank you so very much!
    
Francisco September 16, 2022 At 11:24 pm

Have a weird problem with the like operator.

When it is applied to a multiline cell, it doesn’t come out as positive even when I use the wildcard %%. Do you guys know how to fix it?

- Prashanth September 17, 2022 At 4:52 pm
  
  Hi, Francisco,
  
  Please share the URL of a sample sheet in your next comment/reply. Explain your problem in that Sheet.
  
  Sometimes the blog comment editor doesn’t post a comment that contains a formula that includes comparison operators.
  
Demi July 4, 2022 At 3:30 pm

Hi there,

Fantastic article, nothing else like it online. Thanks so much for putting it together.

How would you make the following search not case-sensitive?

=query(A2:A,"Select A where A Like'"&B1&"'")

I’ve tried wrapping B1 in double % to no avail.

Thank you in advance!

- Prashanth July 5, 2022 At 1:03 pm
  
  Hi, Demi,
  
  This should work.
  
  =query(A2:A,"Select A where lower(A) Like'"&lower(B1)&"'")
  
Sun Fernando June 7, 2020 At 12:09 pm

Hi, can anyone tell me if I want to extract data using a Query, but exclude certain criteria how to do it. In the above example I do not want “Auckland”, but need “Australia” and “Austria” to appear in my results.

Your help is appreciated.

- Prashanth June 8, 2020 At 3:38 pm
  
  Hi, Sun Fernando,
  
  Try the below Query;
  
  =query(A2:A,"Select A where upper(A) matches 'AUSTRALIA|AUSTRIA'")
  
  … or Filter.
  
  =filter(A2:A,REGEXMATCH(upper(A2:A),"AUSTRIA|AUSTRALIA"))
  
D MURALIDHARA REDDY March 9, 2020 At 3:42 pm

We can use lower (or upper) on both sides of Like operator so that upper, lower or Mixed case be entered, ex:
=query(A2:A,"Select A where lower(A) like lower('%Land%')")

- brian August 3, 2020 At 10:21 am
  
  It works really well for me, thanks
  
Ben Ponderin September 22, 2019 At 11:58 pm

Thank you for the informative intro, Prashanth

Antonio April 19, 2019 At 5:23 am

Hello, How can I use wildcard with reference to SEVERAL cells? B1 and B2 and B3 for example.

=query(A2:A,"Select A where A Like'"&B1&"'")

- Prashanth April 19, 2019 At 10:09 am
  
  Hi, Antonio,
  
  Here is that Query formula variation.
  
  =query(A2:A,"Select A where A Like'"&B1&"' or A Like'"&B2&"' or A Like'"&B3&"'")
  
Enzy February 26, 2019 At 4:14 pm

How can I use wildcard with reference to another cell?

e.g

=query(A2:A,"Select A where A like '% B1 %'")

- Prashanth February 26, 2019 At 6:39 pm
  
  Hi, Enzy,
  
  Type %pp% in cell B1 and use the below formula.
  
  =query(A2:A,"Select A where A Like'"&B1&"'")
  
  This will extract the strings like apple, support etc.
  
  Best,