REST filter=startswith not working when filtering using Id field

Question

When filtering a text column like in built Title field using startswith, proper results are returned. Example:

http://aissp2013:111/_vti_bin/ListData.svc/ChangeRules()?$filter=startswith(Title,'Ti')

However, startswith does not seem to work with inbuilt Id field. Following REST queries do not work:

http://aissp2013:111/_vti_bin/ListData.svc/ChangeRules()?$filter=startswith(Id,1)
http://aissp2013:111/_vti_bin/ListData.svc/ChangeRules()?$filter=startswith(Id,'1')

But querying the Id field using eq works:

http://aissp2013:111/_vti_bin/ListData.svc/ChangeRules()?$filter=(Id eq 1)

Is there any way of making Id work with startswith?

Answer 1

you can use the substringof clause. Like this:

$filter=substringof(Id, '1')

More details on the following link: http://wiki.sensenet.com/index.php?title=OData_REST_API

Answer 2

startsWith OData query option could not be applied to ID field since the function startsWith accepts field values of string type only (e.g. Text Field Type).

However you could use a different approach for query multiple items by ID as demonstrated below.

How to query multiple items by ID via SharePoint REST

Option 1. Specify items explicitly using chained and and or operators. Example: /_vti_bin/ListData.svc/Documents?$filter=(ID eq 1) or (ID eq 2) or (ID eq 3)

Option 2. Specify items limit in a query using top operator. Example: /_vti_bin/ListData.svc/Documents?$top=3&$orderby=ID

Option 3. Specify the range using lt, le, gt, ge operators. Example: /_vti_bin/ListData.svc/Documents?$filter=(ID ge 1) and (ID le 3)

Answer 3

Since ID is a longint, you can't. So, you need to put it to a string field. You can't use calculated values, so a SP workflow could do the trick.