PasteSheet icon PasteSheet logo mark — a spreadsheet grid with a curly brace on a green rounded square PasteSheet

Filter Google Sheets by partial match

Sometimes you do not want an exact match — you want every row whose name starts with "jo" or contains "widget." Here is how to do prefix and partial matching in a sheet and over an API.

Last updated

Key facts

  • The QUERY function's where clause supports starts with, ends with, contains, like and matches (regex) — and those string comparisons are case-sensitive. source
  • String matching is case-sensitive by default. Wrap the column in LOWER() — WHERE LOWER(B) STARTS WITH 'jo' — to match regardless of case. source
  • Over the REST API the same match is ?contains[name]=jo for one column, or ?search=widget across every column — matched by column name. Full-text search is a Pro feature; contains works on every plan.

How do you filter rows that start with a value?

In the sheet, use QUERY() with starts with in the where clause: =QUERY(A:C, "select * where A starts with 'Acme'"). It is case-sensitive, and contains works the same way for a partial match anywhere in the value.

Over an API the equivalent is a prefix or partial filter on the column, which is what you want when the match drives a search box, an autocomplete, or an agent looking something up rather than a cell you read with your eyes.

STARTS WITH in the QUERY() function

In-sheet, QUERY() supports STARTS WITH (and CONTAINS) in the WHERE clause:

Google Sheets 
=QUERY(A1:D, "SELECT * WHERE B STARTS WITH 'jo'")

Case-insensitive and NOT STARTS WITH

String comparisons are case-sensitive, so wrap the column in LOWER() to match regardless of case. Put NOT before the column to invert the match:

Google Sheets 
=QUERY(A1:D, "SELECT * WHERE LOWER(B) STARTS WITH 'jo'")

=QUERY(A1:D, "SELECT * WHERE NOT B STARTS WITH 'jo'")

Ends with, contains, and regex

The same WHERE clause has an operator for every kind of partial match — a suffix with ENDS WITH, a substring anywhere with CONTAINS, and a full regular expression with MATCHES:

Google Sheets 
=QUERY(A1:D, "SELECT * WHERE B ENDS WITH 'ltd'")
=QUERY(A1:D, "SELECT * WHERE B CONTAINS 'widget'")
=QUERY(A1:D, "SELECT * WHERE B MATCHES '(?i)acme.*'")

Which operator matches what

All five are string operators in the WHERE clause, and all are case-sensitive unless you wrap the column in LOWER():

Operator Matches Example Case-sensitive
STARTS WITH A prefix Matches “Jones” for prefix jo Yes
ENDS WITH A suffix Matches “Acme Ltd” for suffix ltd Yes
CONTAINS A substring anywhere Matches “Blue Widget Co” for widget Yes
LIKE A wildcard pattern (%, _) Matches “Acme” for pattern a%e Yes
MATCHES A regular expression Matches “ACME Corp” for regex (?i)acme.* Regex-controlled

Partial matching over the API

Over the REST API, use contains[column] for a partial match on a specific column, or search for a full-text match across every column:

terminal 
# names containing "jo"
curl 'https://pastesheet.com/api/your-endpoint-id?contains[name]=jo'

# full-text search across all columns
curl 'https://pastesheet.com/api/your-endpoint-id?search=widget'

Full-text search is available on the Pro plan; exact and contains filters work on every plan. Pair partial matching with sorting to build a complete query, or see the full parameter reference.

Frequently asked questions

How do I filter rows that start with a value?

Use STARTS WITH in the QUERY() function inside the sheet, or the contains[column] parameter over the PasteSheet REST API for a partial match.

Is STARTS WITH case-sensitive?

Yes. String comparisons in QUERY() are case-sensitive, so 'jo' will not match 'Jo'. Wrap the column in LOWER() — WHERE LOWER(B) STARTS WITH 'jo' — to match regardless of case.

How do I match the end of a value or use a regex?

Use ENDS WITH for a suffix, CONTAINS for a substring anywhere, or MATCHES with a regular expression in the WHERE clause. All of them work like STARTS WITH.

How do I exclude rows that start with a value?

Put NOT before the column: WHERE NOT B STARTS WITH 'jo'. Over the API, filter to the rows you do want rather than the ones you do not.

What is the difference between contains and search?

contains[column] matches part of one specific column; search does a full-text match across all columns. Search is a Pro feature.

Sources

Related guides

How to Query Google Sheets With SQL

Query Google Sheets like SQL: the built-in QUERY() function for in-sheet queries, and a REST API for SQL-style filtering, sorting, and grouping from your app.

Google Sheets Query: Sort & Order By

Sort Google Sheets query results by any column, ascending or descending, including by date. Use the QUERY() function or sort and order URL parameters.

Turn Google Sheets Into a REST API (No Code)

Turn a public or restricted Google Sheet into a live JSON REST API with filtering, sorting, and pagination — no backend, no code.

Turn your sheet into an API in minutes

Paste a Google Sheet URL and get a live REST API and MCP server — no backend, no code, free to start.

Start free See pricing