OData substringof or startswith returning all items

Question

I'm trying to filter my results from a Rest Call.

$.ajax({
    type: "GET",
    headers: {
        "Accept": "application/json;odata=verbose"
    },
    dataType: "JSON",
    url: _spPageContextInfo.webServerRelativeUrl + "/_api/lists/getByTitle('Contacts')/items?$select=Title,Id&$startswith('Title','" + request.term + "') eq true",
    success: function (data) {
    },
    error: function (ex) {
    }
});

In my Contacts List i'm trying to retrieve the Title and the Id for Items which start with a String or which have the String somewhere in it, here for example it is the Name of somebody.

I also tried it with substringof:

"/_api/lists/getByTitle('Contacts')/items?$select=Title,Id&$substringof(" + request.term + ",'Title') eq true"

which delivers also the same result.

It gives me all List Items from the List and no Filtering is applied. I build the Url for the Rest after looking here Programming using the SharePoint 2013 REST service Like the Schema given there I think the Url looks ok, but it not seems so :)

Edit:

Applying the $filter like in the OData Uri Conventions gives me the following error:

{"error":{"code":"-1, Microsoft.SharePoint.SPException","message":{"lang":"en-US","value":"The query is not valid."}}}

Tried it with following Query Strings:

_api/lists/getByTitle('Contacts')/items?$select=Title,Id&$filter=substringof(m,'Title') eq true

_api/lists/getByTitle('Contacts')/items?$select=Title,Id&$filter=substringof('m','Title') eq true

_api/lists/getByTitle('Contacts')/items?$select=Title,Id&$filter=substringof('m',Title) eq true

score 19 · Accepted Answer · answered Aug 08 '13 at 13:27

I've managed to get the filter with substringof returning the correct results when I removed the "eq true".

Using one of your query strings, it should work like this:

_api/lists/getByTitle('Contacts')/items?$select=Title,Id&$filter=substringof('m',Title)

I haven't checked any other functions, but at least, the same happens with startswith function.

score 10 · Answer 2 · edited Mar 19 '15 at 22:20

10

For anyone looking at this question, I can report that

/_api/web/lists/GetByTitle('Applications')/items?$filter=startswith(Title,'1AAJ')

IS working for me.

edited Mar 19 '15 at 22:20

Paweł Tomkiel

1,974
2
21
39

answered Mar 19 '15 at 21:44

Keith Hudson

218
2
6

score 0 · Answer 3 · edited Dec 31 '18 at 10:06

0

Check http://www.odata.org/documentation/odata-v2-documentation/uri-conventions/ for the correct uri convention.

Should be

/_api/lists/getByTitle('Contacts')     
/items?$select=Title,Id&$filter=substringof(" + request.term + ",'Title') eq true"

So with the $filter included

edited Dec 31 '18 at 10:06

Martijn

15,791
4
36
68

answered Apr 19 '13 at 14:34

Rolfvm

336
2
9

please see my edit. When I apply the $filter it gives me an error that the query is not in a correct format – Mark Apr 22 '13 at 07:43
Maybe it is because of the combination/order of the $select, it seems it should work. http://sharepoint.mindsharpblogs.com/NancyB/Lists/Posts/Post.aspx?List=b6efd474-248a-4b16-ab88-afdb6fa31b65&ID=25. Could you try with just the filter? – Rolfvm Apr 22 '13 at 09:44
Tried it with only the filter the same Error is returning. Theres probably something missing but I don't get it. When i'm looking at the conventions it looks ok – Mark Apr 22 '13 at 10:50

score 0 · Answer 4 · answered May 10 '13 at 15:00

I tried your query URI on my endpoint and applied some changes: - The second parameter of the substring shouldn't be a string, so I removed the apostropes

After this I get the results:

http://jaydata.org/examples/Northwind.svc/Products?$select=Product_ID,Product_Name&$filter=substringof('CH',Product_Name)

My endpoint is standard WCF Data Service, and the filter is working.

If changing the URI still returns all records, that would be a SherePoint trick I guess. What happens if you put 'zzz' or some random string in the filter?

Tested it also again with that string, but it is only returning the same error "The query is not valid." when i'm using the $filter tag — Mark, Jul 01 '13 at 07:17

lyndon hughey · Answer 5 · 2018-03-08T21:24:03.780

-1

Also, the contains method works and I've had better compatibility with it. The syntax is:

api/People?$filter=contains(LastName,%27Smith%27)&$select=LastName,FirstName

edited Mar 08 '18 at 21:24

answered Feb 07 '18 at 17:40

lyndon hughey

597
6
14

The contains parameter definitely works, as referenced at http://www.odata.org/getting-started/basic-tutorial/. It's one of the easier filter partial matches. I don't have Sharepoint 2013, so I can't test against it, but it work for anyone using a modern and standard implementation of OData. – lyndon hughey Mar 07 '18 at 13:17
2

Sorry my fault, I thought the topic was related to SP 2013. Please can you edit you answer so I can remove my downvote? thanks – Alberto S. Mar 08 '18 at 17:36

OData substringof or startswith returning all items

5 Answers5

Linked