9

I'd like to filter a rest request and get the opposite of:

filter=substringof('foo', Title)

I've tried with a leading "not" and also ending with "eq false" but those it seems are not valid queries. I guess what I'm really lacking is a complete documentation of the filter regular expression syntax.

3 Answers 3

14

It seems like there is a bug with substringof operator in REST/OData web service implementation.

Symptoms

When applying substringof operator to a list items using the following syntax:

/_api/web/lists/getbytitle('Contacts')/items?$filter=substringof('John', Title) eq false

an error occurs:

<m:error 
    xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
    <m:code>-1, Microsoft.SharePoint.SPException</m:code>
    <m:message xml:lang="en-US">The query is not valid.</m:message>
</m:error>

Workaround

You could target the listdata.svc where substringof operator could be applied successfully.

Example:

/_vti_bin/listdata.svc/Contacts?$filter=substringof('Jane',LastName) eq false
1
  • 1
    Thanks Vadim. It seems this bug has not been fixed my Microsoft yet. But the alternative you provided work perfectly.
    – Amit
    Oct 6, 2016 at 5:45
4

The figure below shows the supported OData query options and how to specify supported filters enter image description here

You can find the full documentation here.

The following Url is working fine at my end:

_api/web/Lists?$filter=substringof('Documents',Title) eq false
2
  • 1
    this solution not work for me Jul 7, 2017 at 7:08
  • 1
    This syntax worked for me, using SharePoint 2016 Server with the December 2018 CU. Dec 27, 2018 at 19:53
1

You need to append eq true/false as in this working filter for SharePoint o365:

?$filter=substringof('Johnston', Title) eq true &$select=Id,Title,Email

the eq true is outside the substringof parenthesis.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.