LIKE String Operator in Google Sheets Query and Wildcard Use

The LIKE string operator in Google Sheets Query is used for advanced string comparisons. You can apply it in the WHERE clause to perform flexible text searches.

The LIKE operator supports two wildcards in the Google Sheets QUERY function:

% (percentage)
_ (underscore)

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

The WHERE clause in QUERY filters rows based on specified criteria, and comparison operators play a key role in that process. QUERY supports both simple and string-based comparison operators.

In this tutorial, you’ll learn how to use the LIKE string operator in Google Sheets Query (similar to SQL LIKE) with practical examples.

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

Table of Contents

Using the LIKE String Comparison Operator in Google Sheets Query

Important Notes

The LIKE operator is case-sensitive in Google Sheets Query.
To make it case-insensitive, use the LOWER() or UPPER() scalar functions.
The LIKE operator supports two wildcards only: % and _.

Sample Data

We’ll use a list of country names in column A for most of the examples below.

LIKE String Operator in Google Sheets - Example

Using the `%` (Percentage) Wildcard in QUERY

(Alternative to Asterisk *)

The % wildcard matches zero or more characters of any kind.

Formula 1

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

This formula returns all values in column A, similar to using *.

Formula 2

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

Result:

Australia
Austria
Auckland

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

Formula 3

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

Result:

Oakland
Auckland

This returns values that end with “land”.

Formula 4

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

Result:

Slovakia
Slovenia

This matches text that starts with “Sl” and ends with “ia”, with any characters in between.

These examples demonstrate how to use the % wildcard with the LIKE string operator in Google Sheets Query.

Using the `_` (Underscore) Wildcard in QUERY

(Alternative to Question Mark ?)

The _ wildcard matches exactly one character.

Note: The sample data below is different from the earlier examples.

Example 1

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

This returns names such as:

Ami
Amy

Example 2

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

This matches text with exactly three characters starting with “A”, such as:

Ann
Ash

Case-Insensitive LIKE String Match in Google Sheets Query

Since LIKE is case-sensitive, you can combine it with LOWER() or UPPER().

Example

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

This returns results regardless of text case.

Using Cell References with LIKE in QUERY

Instead of hardcoding criteria inside the formula, you can place the pattern in a cell reference and use it with LIKE.

A detailed explanation is available here:
How to Use Cell Reference in Google Sheets Query

NOT LIKE String Operator in Google Sheets Query

You can negate a LIKE condition by placing the NOT operator before the column identifier.

NOT with Percentage Wildcard

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

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

NOT with Underscore Wildcard

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

This excludes names with exactly three characters starting with “Am” (such as Ami) but will return longer names like:

Ami Santo

Conclusion

The LIKE string operator in Google Sheets Query is a powerful tool for text-based filtering. By combining %, _, and scalar functions like LOWER() or UPPER(), you can handle a wide range of string-matching scenarios efficiently.

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,