SAP Knowledge Base Article - Public

3190437 - Integration Center: Using wild card filter as "_" not working properly

Symptom

You wish to know if you can use a wild card filter as as %_2021%, to fetch data that only has _2021

 

 

Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

 

 

Environment

  • SAP SuccessFactors HXM Suite
  • Integration Center

 

Cause

The feature is currently not supported by integration center filtering. 

Resolution

"_" is not supported in like operator.

The workaround is to use the operators "startswith" or "endswith" instead, but this can be done only in OData API, that means outside Integration center tool.

 

See Also

SAP SuccessFactors HXM Suite OData API: Developer Guide (V2) - $filter - SAP Help Portal

Find below some samples of the usage of toupper and tolower + startswith and endswith in $filters in the ODATA API:

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 SF 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 '%' need 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 use 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'

"_" is a special keyword in like operator, it has the same meaning as "%":

odata/v2/User?$filter=firstName like '%ca%'

equals
odata/v2/User?$filter=firstName like '_ca_'

if the field contains "_", it can't be filtered with like.

One workaround is to use startswith or endswith:

odata/v2/User?$filter=endswith(firstName, '_ca')

odata/v2/User?$filter=startswith(firstName, '_ca')

 

Keywords

wild card filter, underscore, integration center, OData API, startswith, endswith, _ , KBA , LOD-SF-INT-INC-JOB , Integration Center Job , LOD-SF-INT , Integrations , LOD-SF-INT-INC , Integration Center , LOD-SF-INT-ODATA , OData API Framework , How To

Product

SAP SuccessFactors HXM Suite all versions