OData: startswith Function

We're used to operators like equals or and, but we can also have functions to work with our data. These are super useful to filter the data at the source and pull only the information that we need, instead of fetching everything and sorting it out afterward, which is wasteful. The "startswith" function looks at a text field and returns every item whose value begins with the text we provide.

As always, we'll use Power Automate and SharePoint to test, since those are the two connectors most of us reach for, but "startswith" is part of the OData standard. That means the same idea carries over to any RESTful API that speaks OData, even outside Microsoft's platform. It sits alongside its siblings "endswith" and "substringof", each one matching a different part of a string.

Let's check how to use it.

Where to find it?

OData filters live in the advanced options of data actions, not in plain sight. In the SharePoint "Get Items" action you'll find them in the "Filter Query" box, which only shows up once you expand the advanced parameters. That box is where our "startswith" expression goes.

Now that we know where it lives, let's understand how to use it.

Usage

The pattern is always the same. You name the field you want to check, then the text it should begin with.

startswith(<field>, '<value>')

To test and demonstrate the syntax, we'll use a list of random data with fake people.

We've already covered the "equals operator" or "eq operator," so if you have questions, you can check it here.

Let's fetch everyone whose first name is "Aida." We'll use the Title column, which holds the full name of the person.

startswith(Title,'Aida')

Here's the test in Power Automate. We use a table to see the results quickly and check if our filter is working.

Here's the result:

If the "startswith" function was not available, we would need to fetch all items and filter them. Depending on our data, this would take a long time and some effort, but this way, the Flow runs in seconds and returns our results quickly.

Only strings

We can only use the "startswith" function with strings. It makes sense to do so, since there's no real "start" to a number or a date, but it does catch people out. For example, it would be useful if we could find everyone in a certain age group using something like:

startswith(Age,3)

This is just an example. The Flow will return an error if you try to use the function this way. When you need to match numbers or dates, reach for the comparison operators instead, such as the "equals" operator.

Non-intuitive behaviors

Matching ignores case

OData string filters ignore case, so startswith(Title, 'aida') and startswith(Title, 'AIDA') return the same people as startswith(Title, 'Aida'). That is handy, because you don't have to guess how the data was typed. Keep in mind that this forgiveness applies only to the value you're matching, not to the field name, which still has to be exactly right.

It only looks at the start

As the name says, "startswith" checks the beginning of the field and nothing else. If you search for startswith(Title, 'Silva'), you won't find "Aida Silva," because "Silva" sits in the middle of the value. When you need to match text that can appear anywhere inside the field, that's a job for the "substringof" function rather than this one.

Limitations

Field names, not display names

OData filters work on the internal field name, not the friendly display name you see on the form, and the two often differ. In SharePoint, any space in the original column name is stored as _x0020_, so a column shown as "Full Name" is really "Full_x0020_Name" behind the scenes. Use the internal name in your filter, or the query will quietly return nothing.

Here's how to be sure that the field name you're using is correct.

You can then copy and paste the names.

URL size

The filter is sent to the server as part of the URL, and a URL has a practical ceiling of around 2000 characters. A single "startswith" will never get close, but if you chain many conditions together with the "or" operator, you can hit that wall. I'm putting this here for the sake of completeness because 2000 characters is a lot. If you find yourself building a very long filter, it's usually a sign to rethink the approach, perhaps by filtering on a broader condition and trimming the rest later in the Flow.

Troubleshooting Common Errors

The Flow runs without error but returns no items.

• Cause: The filter is using the display name of the column instead of its internal name, so the server matches nothing.
• Solution: Find the internal name (see "Field names, not display names" above) and remember to replace any space with _x0020_.

The Flow fails with an "expression is not valid" message.

• Cause: The value isn't wrapped in single quotes, or the function name is capitalized. OData is case-sensitive about the function name itself.
• Solution: Write the function in lowercase and quote the value, like startswith(Title, 'Aida').

Recommendations

Here are some things to keep in mind.

Index the column you filter on

If you filter a large list on the same field often, add an index to that column in the list settings. SharePoint can use the index to find your matches much faster, and on big lists it's the difference between a Flow that finishes quickly and one that crawls or times out.

Name it correctly

Naming is super important in this case. I see many mistakes because the default view, for example, in the SharePoint "Get Items" action in Power Automate, hides the "advanced options." Therefore, someone who is scanning your Flow may not notice that there are filters.

Always add a comment

If you can, regardless of where you use the expressions, try to add a comment indicating what you are trying to do. Do this before you do the expression so that you think before you do. It's a good practice to map intention with action so that, if something goes wrong, you can check either the initial information or the expression itself. Also, when you're scanning your Flow to understand what it's doing, having a comment will help you understand what is being done without looking at the expression. If you're not familiar with the SharePoint List structure, for example, you'll at least know what is being done.

Final Thoughts

The "startswith" function is a simple but powerful way to filter your data at the source, so your Flows only pull what they need. Keep an eye on your field names and you'll get fast, clean results every time. Give it a try on your own lists and see how much time it saves you.

Back to the OData Reference.

Photo by Start Digital on Unsplash