LIKE String Operator in Google Sheets Query and Wildcard Use

The LIKE string operator in Google Sheets Query is for complex string comparisons.

You can use it with the WHERE keyword for text searches.

The LIKE enables using two wildcards in Google Sheets QUERY function: % (percentage) and _ (underscore).

These two wildcards are similar to the asterisk and question mark used in many other native Google Sheets functions.

The WHERE clause in Query helps filter rows based on given conditions (criteria).

The comparison operators play a vital part in it.

There are complex as well as simple comparison operators.

To learn about the other complex comparison operators in the Query function, please check the section “Additional Resources” at the end of this post.

Let’s see how to use the LIKE (similar to SQL LIKE) complex string comparison operator in Google Sheets Query.

Using LIKE String Comparison Operator in Google Sheets Query

Important Notes:

The LIKE is a case-sensitive string comparison operator for use in the Google Sheets Query function. To bring case insensitivity, use the scalar functions Lower or Upper with the criterion.
As I have already mentioned, it supports two wildcards: % (percentage) and _ (underscore).

Sample Data: We will use this data for a couple of examples below, but not with all.

LIKE String Operator in Google Sheets - Example

The Use of % (Percentage) Wildcard in Query (Alternative to Asterisk)

You can use the percentage wildcard in the LIKE operator to match zero or more characters of any kind.

Formula 1:

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

It’s just like using the wildcard character *. It returns all the country names in column A as it’s.

Formula 2:

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

Result:

Australia
Austria
Auckland

What does it mean? It means the percentage wildcard in LIKE returns all the strings starting with the letter “A”.

Formula 3:

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

Result:

Oakland
Auckland

Formula 4:

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

Result:

Slovakia
Slovenia

Hope the above formulas answer how to use the % wildcard in the LIKE string comparison operator in Google Sheets Query.

The Use of _ (Underscore) Wildcard in Query (Alternative to Question Mark)

Use the underscore wildcard in Query to match any single character. It’s like the use of the wildcard “?”.

Note: I am not following the above sample data here.

Here is one example formula.

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

This would return the names like Ami, Amy, etc.

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

In this formula, I’ve used a double underscore. It can return the names like Ann, Ash, etc.

Hope you have learned now how to use the LIKE string comparison operator in Google Sheets Query.

Finally, if you want to make the above formulas case-insensitive, please follow the below logic.

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

See how I’ve used thelower() scalar function in the formula.

We usually specify a criterion in a cell and refer to that in the formula. You can follow that within the LIKE also.

Here is a detailed tutorial on how to use LIKE with a criterion specified in a cell – How to Use Cell Reference in Google Sheets Query.

Not LIKE String Operator Use in Google Sheets Query

In all the above examples, we can negate the condition by placing NOT before the column identifier.

Here is one example of how to perform a Not LIKE string comparison operation in Google Sheets Query.

Not with Percentage Wildcard:

Assume there are country names in column A.

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

The above formula will return the country names that don’t start with the letter A.

Not with Underscore Wildcard:

Assume there are a few names in column A.

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

This formula will return any names that start with “Am” and have 3+ characters.

It won’t return the name “Ami”, But would return “Ami Santo”.

Additional Resources

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,