web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Substring with case In...
Small and medium business | Business Central, N...
Suggested answer

Substring with case Insensitive filter is not working OData V4

(0) ShareShare
ReportReport
Posted on by vinoth vk 15

Working on the filter options through ODataV4 endpoints.

In the API I have a field name called Description and it has the value.

No: "BC001",

Description: "Black poly chair"

No: "BC002",

Description: "Dining Chair "

If I search for the word "Chair". that should bring all the Items ( Case shouldn't matter).

This is the Filter Option I'm using for it.

$filter=contains(tolower(Description),'chair').

Actual URL I'm trying:

api.businesscentral.dynamics.com/.../

Note: $filter= tolower(Description) eq 'dininG chaiR ') is Working, showing empty result.

When I apply the same, under the contain option., that time only its not working.

Example : In the below example they provided the solution that is for services.odata.org. I tried the same code for filter. but still I'm getting empty result.

https://github.com/OData/ODataSamples/issues/66#issue-257575195

Can you help me for solve this.

Thank in Advance

Categories:
Dynamics 365 Business Central
I have the same question (0)
  • Lars Lohndorf-Larsen Profile Picture
    Lars Lohndorf-Larsen on at
    RE: Substring with case Insensitive filter is not working OData V4

    Hello,

    I am interested too - I can get this to work:

    {{BaseURI}}/companies(2e396114-00ff-ea11-bc53-00155df3a615)/items?$filter=tolower(displayName) eq tolower('LONDOn Swivel Chair, blue')

    but this returns nothing:

    {{BaseURI}}/companies(2e396114-00ff-ea11-bc53-00155df3a615)/items?$filter=contains(tolower(displayName),tolower(%27chair%27))

    I know this does not help you much, but I will try to research a bit and return here if I find out something, and meanwhile I hope others also have some ideas.

  • Frank Hillsnipe Profile Picture
    Frank Hillsnipe 1,332 on at
    RE: Substring with case Insensitive filter is not working OData V4

    As of September 30th 2022 - This is a documented limitation, note the "Nested function calls" section in the article.

    learn.microsoft.com/.../use-filter-expressions-in-odata-uris

    "Nested function calls in filter clauses aren't supported in the current OData implementation. This means that filter clause expressions like contains(tolower(field), 'some') don't return the expected results - in this case a partial case-insensitive text search - but will instead either throw an error or return an undefined result."

    If anyone has a way around this issue I would greatly appreciate it.

  • Suggested answer
    awicz Profile Picture
    awicz 19 on at
    Substring with case Insensitive filter is not working OData V4
    Use the @ symbol to enable case-insensitive results:
     
    $filter=tolower(Description) eq 'dininG chaiR'
     
  • Suggested answer
    NR-10050744-0 Profile Picture
    NR-10050744-0 4 on at
    Substring with case Insensitive filter is not working OData V4
    Just replying as this was top result when I was searching for an answer as this might help someone else. 
     
    I acheived this by setting an equals filter but wrap the search term in '@*....*'
     
    So the filter above would be $filter = Description eq '@*chair*'

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
Sumit Singh Profile Picture

Sumit Singh 2,674

#2
Rishabh Kanaskar Profile Picture

Rishabh Kanaskar 2,580

#3
YUN ZHU Profile Picture

YUN ZHU 2,115 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans