# 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: 2026-07-15 · Source: * ## 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](https://developers.google.com/chart/interactive/docs/querylanguage)) - String matching is **case-sensitive by default**. Wrap the column in `LOWER()` — `WHERE LOWER(B) STARTS WITH 'jo'` — to match regardless of case. ([source](https://developers.google.com/chart/interactive/docs/querylanguage)) - 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: ```bash =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: ```bash =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`: ```bash =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: ```bash # 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](https://pastesheet.com/guides/google-sheets-query-order-by) to build a complete query, or see the [full parameter reference](https://pastesheet.com/guides/google-sheets-rest-api). ## 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 - [Query Language Reference (Google Visualization API)](https://developers.google.com/chart/interactive/docs/querylanguage) — Google - [QUERY function](https://support.google.com/docs/answer/3093343) — Google Docs Editors Help ## Related guides - [How to Query Google Sheets With SQL](https://pastesheet.com/guides/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](https://pastesheet.com/guides/google-sheets-query-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)](https://pastesheet.com/guides/google-sheets-rest-api) — Turn a public or restricted Google Sheet into a live JSON REST API with filtering, sorting, and pagination — no backend, no code. --- [PasteSheet](https://pastesheet.com) turns any Google Sheet into a live REST API and MCP server for AI agents — no backend, no code. Canonical HTML version of this page: