ODATA - how do you filter when your numbers are formatted as text?

[phkGZ]

Heyyyoo

I have a column which has account numbers in it - 1010, 1020, 1030, 1500, 1780 and so forth.

I am trying to use an ODATA filter to get only rows were the account number less than 2000.

So "&$filter=Account_No lt 2000" should work. Except it doesn't because it seems the source data is formatted as text (from business central).

Ive googled and googled and cannot figure out how to convert string to number - seems like this is not possible. Is there some workaround?

Also tried to create my own data set in business central, but it does not allow to format the columns differently.

[v-eqin-msft]

Hi @phkGZ ,

According to the official document, If the field 's type is String, '' is needed. For example:

$filter=itemKey ne 'searchValue'

You may try to use  Edm.Int32 to convert string to a numeric type.

For more information, please kindly refer to:

$filter Query · Personium

OData expression syntax reference - Azure Cognitive Search | Microsoft Docs

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

OData comparison operator reference - Azure Cognitive Search | Microsoft Docs

Comparing string data

Strings can be compared in filters for exact matches using the eq and ne operators. These comparisons are case-sensitive.