Do you know all the ins and outs of Google Sheets Query? If you’re an avid user, you might be familiar with wildcard usage, but do you know how to achieve partial matches or substring matches? In this article, we’ll dive into the world of CONTAINS substring matching in Google Sheets Query and unlock its hidden potential.
How to Use the Contains Substring Match in Google Sheets Query
Let’s start by exploring how to filter data based on a partial-string match using the CONTAINS substring comparison operator. Imagine you have a column in your Google Sheets that contains various fruit names. You want to filter all the rows that contain the word “Mango” in that column. Here’s the Query formula that will do the trick:
=query(A1:B,"Select A where A contains 'Mango'")This formula will return the rows that match the partial-string criteria, in this case, rows 4 and 5. But that’s just the beginning! Let’s take a look at a few more examples of how you can use the CONTAINS substring match in Google Sheets Query.
I think the above example is pretty self-explanatory, but there are a few peculiarities to keep in mind when using this complex comparison operator.
1. Contains in Query Where Clause for Partial Match
The Query CONTAINS substring match allows you to find a substring within a text, regardless of its position. It will test whether the substring is present anywhere in the text, whether it’s at the beginning, in the middle, or at the end.
For example, if you want to find rows that contain the word “blog” in your data, you can use the following Query formula:
=query(A1:A,"Select A where A contains 'blog'")This formula will return all the rows that include the word “blog” within the column.
2. Case Insensitive Contains in Query Where Clause
By default, the CONTAINS substring match in Google Sheets Query is case-sensitive. It treats capital and lowercase letters as distinct characters. However, you can overcome this by using the UPPER or LOWER scalar functions.
For instance, if you have the word “magazine” in your column A and you want to find it regardless of case, you can modify the formula as follows:
=query(A1:A,"Select A where UPPER(A) contains 'MAGAZINE'")Alternatively, you can use the LOWER scalar function:
=query(A1:A,"Select A where lower(A) contains 'magazine'")Now you can search for the word without worrying about uppercase or lowercase discrepancies.
How to Use Does Not Contain in Substring Match in Query
But what if you want to find rows that do not contain a specific substring in a column? Fear not, for there is a solution! You can combine the NOT logical operator with the CONTAINS substring match in your Query formula.
Here’s an example of how to find rows that do not contain the word “blog”:
=query(A1:A,"Select A where NOT A contains 'blog'")By using the NOT logical operator, you can exclude rows that meet the specified criteria.
That’s it! You’ve now mastered the art of using CONTAINS substring matching in Google Sheets Query. But remember, this is not the only solution for partial matches. If you’re hungry for more knowledge, check out the resources below. Happy querying!
