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

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.

• SAP SuccessFactors HXM Suite
• Integration Center

The feature is currently not supported by integration center filtering. 

"_" 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.

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')

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-ODATA , OData API Framework , LOD-SF-INT-INC , Integration Center , How To