TM1 indeed follows more or less the VBA specification of the Instr function (which in retrospect is a bit of an unexpected choose - which predates my involvement - given the historical case and space insensitiveness of the product). I'm saying 'more-or-less' because the VBA specification by default uses 'binary' comparison, which implies case sensitiveness, whereas specifying this forth parameter as 1 implies 'text' compare which then also seems to imply case insensitive compare.
TM1 simply converts the string parameters to lower case before doing the operation if this forth argument is set to 1 (ignoring any local specific stuff which VBA in case of text compare seems to include as well).
Apart from improving the way we do comparison itself (read: be better about how we comparing UTF-8 encoded Unicode strings) potentially, I don't see why we would change the behavior of this Instr function itself. No changes are anticipated right now.
------------------------------
Hubert Heijkers
------------------------------
Original Message:
Sent: Thu April 21, 2022 06:36 AM
From: George Tonkin
Subject: INSTR() and MDX
When using the INSTR function, the comparison is typically case-sensitive unless you use the 4th argument and set the flag to 1.
Not sure that this has been documented and when my luck will run out with this.
Microsoft MDX documentation for argument 4 says:
Compare
(optional) An integer value. This argument is always ignored. It is defined for compatibility with other Instr functions in other languages.
The questions are thus:
a) In TM1 MDX queries, does 1 as the 4th argument mean case-insensitive search?
b) With the stringer typing etc. anticipated, is this likely to change? If so, how?
------------------------------
George Tonkin
------------------------------
#PlanningAnalyticswithWatson