Google Sheets QUERY: Partial Search

Until now, the condition of WHERE used the = operator to test if one value was equal to another.

As for example here with the following query that only returns a result when the name (C) is equal to "Ava":

=QUERY(DB!A1:G15,"SELECT * WHERE C = 'Ava'")

Partial search with LIKE

To perform a partial search, use the LIKE operator and add one or more % to the value searched for depending on the type of search.

For example, to search for all users whose username contains an "e", enter the following query (adding a % on either side of the "e" to indicate that there may be other characters at this place):

=QUERY(DB!A1:G15,"SELECT * WHERE C LIKE '%e%'")

You may notice that the user Emma does not appear among the function results because it is case sensitive (to ignore uppercase, it is possible to use a function, we will come back to this later in this course).

To now search for all users whose username ends with "ia", enter the following query (adding a % only in front of "ia"):

=QUERY(DB!A1:G15,"SELECT * WHERE C LIKE '%ia'")

google sheets query where like ends with

The character % therefore replaces zero, one or more random characters.

It is also possible to use the character _ to replace a random character.

For example, the following query will search for all users whose name contains a "a" as the second character:

=QUERY(DB!A1:G15,"SELECT * WHERE C LIKE '_a%'")

google sheets query where like underscore

NOT

In a previous example, the function returned all users whose username ended with "ia".

To obtain the opposite result (all users whose username does not end with "ia"), add NOT in front of the condition:

=QUERY(DB!A1:G15,"SELECT * WHERE NOT C LIKE '%ia'")

CONTAINS

Other operators allow to do more or less similar searches. This is the case with the CONTAINS operator which allows to search if the text contains a value (similar to LIKE with two %).

For example, search for users whose name contains "am":

=QUERY(DB!A1:G15,"SELECT * WHERE C CONTAINS 'am'")

STARTS WITH

The STARTS WITH operator allows to search if the text starts with a value (similar to LIKE with a % at the end).

For example, search for users whose number of messages starts with a "1":

=QUERY(DB!A1:G15,"SELECT * WHERE E STARTS WITH 1")

google sheets query where starts with like

ENDS WITH

The ENDS WITH operator allows to search if the text ends with a value (similar to LIKE with a % at the beginning).

For example, search for users whose email address ends with "a@example.com":

=QUERY(DB!A1:G15,"SELECT * WHERE G ENDS WITH 'a@example.com'")

google sheets query where ends with like

MATCHES

The MATCHES operator allows to test the value using a regular expression.

For example, search for users whose email address contains at least one "l" placed before the "@":

=QUERY(DB!A1:G15,"SELECT * WHERE G MATCHES '.*l.*@.*'")

google sheets query where matches regex like