SAP Knowledge Base Article - Public

2772208 - OData API filter value selected in API Request is always case sensitive

Symptom

OData API filter value which you are selecting for $FILTER parameter is always case sensitive.

Example: There is a USER in your SFSF System: "Aanya Singh".

Use Cases:

1.  Expected Data in Response:

 /odata/v2/User?$select=userId,firstName,lastName,username&$filter=(firstName eq 'Aanya' and lastName eq 'Singh')

 /odata/v2/User?$select=userId,firstName,lastName,username&$filter=firstName eq 'Aanya

2. No Data in Response:

/odata/v2/User?$select=userId,firstName,lastName,username&$filter=(firstName eq 'aanya' and lastName eq 'Singh')

/odata/v2/User?$select=userId,firstName,lastName,username&$filter=firstName eq 'aanya'

 

Environment

SAP SuccessFactors

Resolution

This is expected behaviour of OData API as filter value that you are selecting is always case sensitive.

Hence, Use Case 2 will not return any data,  i.e. 'Aanya' and 'aanya' will be considered as different names while searched by ODATA API calls.

Additional Information:

  • To support case insensitive search in OData API, we provided support for toupper() and tolower(), meanwhile 'like' keyword is also needed in the search.
  • 'like' is not an official supported operator in OData protocol v2.0, but SFSF OData API decided to support this.
  • The OData 'like' operator works exactly same with the 'like' in SQL, below are some examples:
    • odata/v2/User?$filter=firstName like '%ca%'
    • odata/v2/User?$filter=firstName like '%ca'
    • odata/v2/User?$filter=firstName like 'ca%'
  • Note that '%' needs be encoded in the HTTP URL, so the real request will be like:
    • odata/v2/User?$filter=firstName like '%25ca%25'
    • odata/v2/User?$filter=firstName like '%25ca'
    • odata/v2/User?$filter=firstName like 'ca%25'
  • Also, 'like' operation is case sensitive, so using the below two queries will get same result:
    • odata/v2/User?$filter=toupper(firstName) like '%25CA%25'
    • odata/v2/User?$filter=tolower(firstName) like '%25ca%25'

Keywords

Case Sensitive, Lower Case, Upper Case, ODATA API , KBA , LOD-SF-INT-ODATA , OData API Framework , LOD-SF-INT , SF Integrations - EC Payroll, Boomi/ HCI, API , Problem

Product

SAP SuccessFactors HCM Core all versions