OData is a widely recognized open standard for accessing data over the Internet. The OData protocol offers robust features for querying data through URLs, akin to SQL. This article aims to provide a quick understanding of how to query and filter data using OData, and illustrates how its features align with commonly used SQL functionalities.

Main Odata Features

Feature	SQL	OData
Querying specific table columns	SELECT ENAME, JOB, SAL FROM People	/People?$select=ENAME,JOB,SAL
Querying the second 5 records	SELECT * FROM Accounts ORDER BY (SELECT NULL) OFFSET 5 ROWS FETCH FIRST 5 ROWS ONLY	/Accounts?$top=5&$skip=5
Ordering Data	SELECT * FROM WorkOrders ORDER BY ActualStartDate, DESC, SAL	/WorkOrders ?$orderby=ActualStartDate, desc,SAL
Querying joined tables	SELECT * FROM Dept LEFT OUTER JOIN Accounts ON Dept.DEPTNO = Accounts.DEPTNO	/Depts?$expand=Accounts
Filtering data	SELECT * FROM PurchaseOrders WHERE (SAL/2 > 500 AND OrderedDate<= ’01/01/1985′) OR (COMM IS NOT NULL AND ENAME LIKE ‘J%’)	/PurchaseOrders?$filter=(SAL div 2 gt 500 and OrderedDate le 1985-01-01) or (COMM ne null and startswith(ENAME,’J’))
Aggregating data	SELECT SUM(SAL) AS Sum, MAX(SAL) AS Max, Min(SAL) AS Min, AVG(Sal) AS Avg FROM PartStockReplenishment	/PartStockReplenishment?$apply=aggregate(SAL with sum as Sum,SAL with max as Max,SAL with min as Min,SAL with average as Avg)

 

 

Filter Expressions
OData protocol supports several mathematical, logical, etc., operators and functions in the $filter expression. Here you can find a brief list of these operators and functions that you can use in your OData requests and their SQL analogs. Note that they are case-sensitive in OData requests.

Sample URL
/Emps?$filter=(SAL div 2 gt 500 and HIREDATE le 1985-01-01) or (COMM ne null and startswith(ENAME,’J’))

Operations

SQL	Odata
=	eq
!=	ne
>	gt
>=	ge
<	lt
<=	le
AND	and
OR	or
NOT	not
+	add
-	sub
*	mul
/	div
%	mod
()	()
IS NULL	eq null
IS NOT NULL	ne null
X LIKE ‘%Y%’	OData v1 – v3: substringof(‘Y’,X) OData v4: contains(X,’Y’)
X LIKE ‘Y%’	startswith(X,’Y’)
X LIKE ‘%Y’	endswith(X,’Y’)

String Functions

SQL	OData
LEN(X)	length(X)
CHARINDEX(X,’Y’)	indexof(X,’Y’)
REPLACE(X,’Y’,’Z’)	replace(X,’Y’,’Z’)
SUBSTRING(X,2,3)	substring(X,2,3)
LOWER(X)	tolower(X)
UPPER(X)	toupper(X)
TRIM(X)	trim(X)
CONCAT(X,Y)	concat(X,Y)

 

Date Functions

SQL	OData
DATEPART(year,X)	year(X)
DATEPART(month,X)	month(X)
DATEPART(day,X)	day(X)
DATEPART(hour,X)	hour(X)
DATEPART(minute,X)	minute(X)
DATEPART(second,X)	second(X)

 

Mathematical Functions

SQL	OData
ROUND(X)	round(X)
FLOOR(X)	floor(X)
CEILING(X)	ceiling(X)