The STARTS WITH is a string comparison operator for prefix match in the Google Sheets Query function. What is its role?

This complex comparison operator helps users to filter rows containing partial matches.

For example, it helps u to filter names that start with or do not start with a specific letter(s) or strings in a column.

It’s a detailed Google Sheets tutorial on using the STARTS WITH prefix match in Google Sheets Query.

Also, you can learn how to negate it using the NOT logical operator in Query.

Introduction to String Comparison in Google Sheets Query

In the Query Where clause, we can use different string comparison operators to filter rows that match a specified condition.

The STARTS WITH is one such ‘complex’ spring comparison operator.

Here are the other popular string comparison operators.

• How to Use LIKE String Operator in Google Sheets Query.
• CONTAINS Substring Match in Google Sheets Query for Partial Match.
• Matches Regular Expression Match in Google Sheets Query.
• Ends with and Not Ends with Suffix Match in Query.
• Simple Comparison Operators in Sheets Query.

How to Use the STARTS WITH Prefix Match in Google Sheets Query

In the below sample data, I am applying the Query STARTS WITH Prefix match in the second column.

The first column contains employee names, and the second column their assigned department.

I want to filter all the names (persons), who are working in the engineering department.

Formula:

=Query(A1:B,"Select * Where B Starts with 'Engineering'",1)

This formula will return all the rows containing the prefix “Engineering” in the second column.

It’s not the only complex string comparison operator in Google Sheets Query for this type of filtering data.

We can use the MATCHES regular expression match with Query as follows.

=Query(A1:B,"Select * Where B matches 'Engineering.*'",1)

Now here is a different scenario.

I want to filter all the rows that do not start with “Engineering” in the second column.

Let’s see how to do that.

How to Use NOT STARTS WITH Prefix Match in Google Sheets Query

If we use the above same sample data, the formula would be like this.

=Query(A1:B,"Select * Where not B Starts with 'Engineering'",1)

Please see the positioning of the Not logical operator. It should be before the column identifier.

Can we apply the same logic and use the MATCHES instead of NOT STARTS WITH in the Query function?

You can. Here you go.

=Query(A1:B,"Select * Where not B matches 'Engineering.*'",1)

The prefix match is in a text column in all the above examples. Of course, that justifies the usage of a string comparison operator.

 Does this work in a number or date column?

I think such a type of comparison may not come in real-life use.

Still, out of curiosity, let’s see whether this string comparison operator may or may not work in a number column first.

Query String Comparison in a Number or Date Column

STARTS WITH Example in Number Column:

In cell range A2:A, I have numbers from 100 to 150 in sequential order.

The below formula that uses the STARTS WITH prefix match would return the numbers 110 to 119.

=query(A1:A,"Select * Where A Starts with 11",1)

NOT STARTS WITH Example in Number Column:

If you negate the Starts With, the result would be numbers from 100 to 109 and 120 to 150.

=query(A1:A,"Select * Where not A Starts with 11",1)

STARTS WITH String Comparison Operator in a Date Column

It won’t work in a date column.