30

Does OData specify whether filter conditions on string fields are to be evaluated case-sensitively or case-insensitively?

Example: (from the docs)

/Suppliers?$filter=Address/City eq 'Redmond'

Is this expected to be case-sensitive or not?

If I want to offer both options, how can this be expressed? There is a tolower() function that can be used like:

/Suppliers?$filter=tolower(Address/City) eq 'redmond'

or

/Suppliers?$filter=tolower(Address/City) eq tolower('Redmond')

Isn't there a more concise way to express case-insensitive matching?

Coding Mash
  • 3,338
  • 5
  • 24
  • 45
eseib
  • 323
  • 1
  • 4
  • 6
  • I get the following error using `tolower` on `EMailAddress1` field of `Contact` entity: `Invalid 'where' condition. An entity member is invoking an invalid property or method.` Any ideas? – Zaid Masud Jan 23 '14 at 17:30

3 Answers3

34

The "eq" operator is supposed to be case sensitive. Usage of tolower (or toupper) is the currently recommended way of doing this.

Vitek Karas MSFT
  • 13,130
  • 1
  • 34
  • 30
  • 1
    I get the following error using `tolower` on `EMailAddress1` field of `Contact` entity: `Invalid 'where' condition. An entity member is invoking an invalid property or method.` Any ideas? – Zaid Masud Jan 23 '14 at 17:29
  • How please?, need example. – Mike Sep 01 '15 at 08:47
  • 1
    @Mike - Here's an example: /people?$filter=tolower(Name) eq tolower('JAmes') - this should give you all people (or Person objects) whose name is 'James' (case insensitive, could be 'JAMES', 'james', 'JamES', etc. Hope this will help :) – Yulian Nov 03 '15 at 15:26
  • Does this work with other operators, etc., `stringof`? – zygimantus Oct 30 '18 at 10:52
  • 1
    Take care when using tolower in requests which are combined with an IQueryable and entity framework. LOWER leads to a bad performance when querying a lot of data. – Alexander Jan 16 '20 at 14:02
1

I would expect this is depended on your database collation setting, as the odata service is just performing a query. If Vitek's answer is correct, then odata is doing some post query filtering on the result set, and that should be strange, right?

Rolfvm
  • 336
  • 2
  • 9
  • This is true of many `IQueryable` implementations that pass the query through to the underlying SQL provider, but the specification does not mention case sensitivity at all so the behaviour will be heavily dependant on the implementation, in this case OP has not provided _any_ implementation detail. – Chris Schaller Feb 09 '22 at 22:19
0

It doesn't depend on database. Even if you perform query against database in case in-sensitive way then OData will do his own additional filtering and filter your data out.

Mariusz Gorzoch
  • 479
  • 6
  • 8
  • 1
    This is misleading, the specification does not mention case sensitivity at all, case and accept sensitivity are _entirely_ dependant on the implementation. – Chris Schaller Feb 09 '22 at 22:14