8

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.

13

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
| improve this answer | |
  • 1
    Thanks Vadim. It seems this bug has not been fixed my Microsoft yet. But the alternative you provided work perfectly. – Amit Oct 6 '16 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
| improve this answer | |
  • 1
    this solution not work for me – Vishal Thakkar Jul 7 '17 at 7:08
  • 1
    This syntax worked for me, using SharePoint 2016 Server with the December 2018 CU. – Chris Odegard Dec 27 '18 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.

| improve this answer | |

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.