The default collation for strings is SQLUPPER, which allows non-case-sensitive searches. So assuming the following table, named foo:

All following sql queries will return exactly the same value in myBar, which will be exactly Hello.

SELECT bar As myBar FROM foo where bar = 'HELLO'

SELECT bar As myBar FROM foo where bar = 'hello'

SELECT bar As myBar FROM foo where bar = 'heLLo'

What I need is something very similar but ignoring leading zeroes when searching.

So the following two queries shall return 00123AB as myBar.

SELECT bar As myBar FROM foo where bar = '00123AB'

SELECT bar As myBar FROM foo where bar = '123AB'

As you can see, the desired behavior is the same, just different transformation. Whether there are few to none use cases where applying this transformation makes sense is another discussion.

Normally I would implement this behavior in the business logic, not in the data model. But this is a change that we need to implement in a really legacy part with a lot of existing business logic. So having the possibility of implementing it that way would save a LOT of effort.

I don't see how I can get this behavior by overriding the methods DisplayToLogical/LogicalToDisplay and/or LogicalToStorage/StorageToLogical.

Thank you for your answer Robert.

This was my initial approach as well. The problem is that you need to use the calculated property to filter and ordering, but the original one to get the value. This will be confusing for the development, and may lead to some issues in the future.

This is why I thought about COLLATION, because it provides the desired behavior, I just need a custom transformation :)