SharePoint ODATA Filter

OData query operators supported by the SharePoint REST service

The following figure shows supported OData “The Open Data Protocol (OData) enables the creation of REST-based data services that allow resources identified using Uniform Resource Identifiers (URIs) and defined in a data model to be published and manipulated by web clients using simple HTTP messages . As always, Wikipedia knows a lot about this: https://de.wikipedia.org/wiki/Open_Data_Protocol query options.

ascThe abbreviation is used to sort data values: ASC = sort in ascending order (lowest value first) and descThe abbreviation is used to sort data values: DESC = sort in descending order (largest value first) can be used for sorting!

Helpful links on this topic:

Further examples:

Select a range of values	Copyfilter=Entry_No gt 610 and Entry_No lt 615Query on GLEntry service. Returns entry numbers 611 through 614.	..
And	Copyfilter=Country_Region_Code eq 'ES' and Payment_Terms_Code eq '14 DAYS'Query on Customer service. Returns customers in Spain where Payment_Terms_Code=14DAYS.	&
Or	Copyfilter= Country_Region_Code eq 'ES' or Country_Region_Code eq 'US'Query on Customer service. Returns customers in Spain and the United States.ImportantYou can use OR operators to apply different filters on the same field. However, you cannot use OR operators to apply filters on two different fields.	|
Less than	Copyfilter=Entry_No lt 610Query on GLEntry service. Returns entry numbers that are less than 610.	<
Greater than	Copyfilter= Entry_No gt 610Query on GLEntry service. Returns entry numbers 611 and higher.	>
Greater than or equal to	Copyfilter=Entry_No ge 610Query on GLEntry service. Returns entry numbers 610 and higher.	>=
Less than or equal to	Copyfilter=Entry_No le 610Query on GLEntry service. Returns entry numbers up to and including 610.	<=
Different from (not equal)	Copyfilter=VAT_Bus_Posting_Group ne 'EXPORT'Query on Customer service. Returns all customers with VAT_Bus_Posting_Group not equal to EXPORT.	<>
endswith	Copyfilter=endswith(VAT_Bus_Posting_Group,'RT')Query on Customer service. Returns all customers with VAT_Bus_Posting_Group values that end in RT.	*
startswith	Copyfilter=startswith(Name, 'S')Query on Customer service. Returns all customers names beginning with “S”.
substringof	Copyfilter=substringof(Name, ‘urn’)Query on Customer service. Returns customer records for customers with names containing the stringA string is a data type that contains a character string. Detailed information is available here: https://www.inf.hs-flensburg.de/lang/prog/string.htm#:~:text=Strings%20sind%20Objekte,ein%20Objekt%20der%20Klasse%20String. “urn”.
length	Copyfilter=length(Name) gt 20Query on Customer service. Returns customer records for customers with names longer than 20 characters.
indexof	Copyfilter=indexof(Location_Code, ‘BLUE’) eq 0Query on Customer service. Returns customer records for customers having a location code beginning with the stringA string is a data type that contains a character string. Detailed information is available here: https://www.inf.hs-flensburg.de/lang/prog/string.htm#:~:text=Strings%20sind%20Objekte,ein%20Objekt%20der%20Klasse%20String. BLUE.
replace	Copyfilter=replace(City, 'Miami', 'Tampa') eq 'CODERED'
substring	Copyfilter=substring(Location_Code, 5) eq 'RED'Query on Customer service. Returns true for customers with the stringA string is a data type that contains a character string. Detailed information is available here: https://www.inf.hs-flensburg.de/lang/prog/string.htm#:~:text=Strings%20sind%20Objekte,ein%20Objekt%20der%20Klasse%20String. RED in their location code starting as position 5.
tolower	Copyfilter=tolower(Location_Code) eq 'code red'
toupper	Copyfilter=toupper(FText) eq '2ND ROW'
trim	Copyfilter=trim(FCode) eq 'CODE RED'
concatthe Concat Function concatenates the result of a formula in all Records applied to a table, resulting in a single string. Use this function to summarize the strings of a table as the Sum -Function with numbers makes. Further information is available here: https://docs.microsoft.com/de-de/powerapps/maker/canvas-apps/functions/function-concatenate	Copyfilter=concat(concat(FText, ', '), FCode) eq '2nd row, CODE RED'
day	Copyfilter=day(FDateTime) eq 12
month	Copyfilter=month(FDateTime) eq 12
year	Copyfilter=year(FDateTime) eq 2010
hour	Copyfilter=hour(FDateTime) eq 1
minute	Copyfilter=minute(FDateTime) eq 32
second	Copyfilter=second(FDateTime) eq 0
round	Copyfilter=round(FDecimal) eq 1
floor	Copyfilter=floor(FDecimal) eq 0
ceiling	Copyfilter=ceiling(FDecimal) eq 1

Dieser Beitrag ist auch verfügbar auf: Deutsch (German)