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

Hi George,

Funnily enough I was having the exact same thoughts, particularly with regard to question b! :)

regards,

Mark

------------------------------
Mark Wragg
------------------------------

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
------------------------------

Thanks for the feedback Hubert and very glad to hear that I will not be Grep'ing or FindStr'ing across many instances to try and locate any MDX to be updated.

------------------------------
George Tonkin
------------------------------

Original Message:
Sent: Fri April 22, 2022 04:25 AM
From: Hubert Heijkers
Subject: INSTR() and MDX

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
------------------------------

I will add a reference to this topic since it might still be relevant:
https://www.tm1forum.com/viewtopic.php?f=3&t=11598

------------------------------
Wim Gielis
------------------------------

Original Message:
Sent: Fri April 22, 2022 04:34 AM
From: George Tonkin
Subject: INSTR() and MDX

Thanks for the feedback Hubert and very glad to hear that I will not be Grep'ing or FindStr'ing across many instances to try and locate any MDX to be updated.

------------------------------
George Tonkin
------------------------------


Original Message:
Sent: Fri April 22, 2022 04:25 AM
From: Hubert Heijkers
Subject: INSTR() and MDX

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

Relevant in the sense of what exactly Wim?

Instr is a documented MDX function so it is not a surprise that it exists in TM1 as well I hope. The surprising part might be related to the fact that the first and the forth parameter are optional (as opposed to the last two), leaving it a bit in the middle what happens when you pass three parameters. TM1 presumes the parameters are provided in the order of the optional parameters (pretty standard for languages that don't allow specifying which parameter one is talking about) and therefore presumes if three parameters are passed it's the start index (first), text and pattern parameters. This is why all four parameters need to be specified if one wants to specify the compare type (forth) parameter as well. 

Am I missing something else Wim?

------------------------------
Hubert Heijkers
------------------------------

Original Message:
Sent: Sat April 23, 2022 05:54 AM
From: Wim Gielis
Subject: INSTR() and MDX

I will add a reference to this topic since it might still be relevant:
https://www.tm1forum.com/viewtopic.php?f=3&t=11598

------------------------------
Wim Gielis
------------------------------


Original Message:
Sent: Fri April 22, 2022 04:34 AM
From: George Tonkin
Subject: INSTR() and MDX

Thanks for the feedback Hubert and very glad to hear that I will not be Grep'ing or FindStr'ing across many instances to try and locate any MDX to be updated.

------------------------------
George Tonkin


Original Message:
Sent: Fri April 22, 2022 04:25 AM
From: Hubert Heijkers
Subject: INSTR() and MDX

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

Hello Hubert,

I was referring to that topic, on Instr just as this topic, since apparently there are different results in TM1 clients for the same expression.
I haven't tested it then and also not now, but I would think this will still be the case (for users in the Subset Editor and active forms).

------------------------------
Wim Gielis
------------------------------

Original Message:
Sent: Mon April 25, 2022 06:06 AM
From: Hubert Heijkers
Subject: INSTR() and MDX

Relevant in the sense of what exactly Wim?

Instr is a documented MDX function so it is not a surprise that it exists in TM1 as well I hope. The surprising part might be related to the fact that the first and the forth parameter are optional (as opposed to the last two), leaving it a bit in the middle what happens when you pass three parameters. TM1 presumes the parameters are provided in the order of the optional parameters (pretty standard for languages that don't allow specifying which parameter one is talking about) and therefore presumes if three parameters are passed it's the start index (first), text and pattern parameters. This is why all four parameters need to be specified if one wants to specify the compare type (forth) parameter as well. 

Am I missing something else Wim?

------------------------------
Hubert Heijkers
------------------------------


Original Message:
Sent: Sat April 23, 2022 05:54 AM
From: Wim Gielis
Subject: INSTR() and MDX

I will add a reference to this topic since it might still be relevant:
https://www.tm1forum.com/viewtopic.php?f=3&t=11598

------------------------------
Wim Gielis


Original Message:
Sent: Fri April 22, 2022 04:34 AM
From: George Tonkin
Subject: INSTR() and MDX

Thanks for the feedback Hubert and very glad to hear that I will not be Grep'ing or FindStr'ing across many instances to try and locate any MDX to be updated.

------------------------------
George Tonkin


Original Message:
Sent: Fri April 22, 2022 04:25 AM
From: Hubert Heijkers
Subject: INSTR() and MDX

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

Hi Wim,

None of the clients mentioned in that topic, AFAIK, interpret the MDX themselves in any way shape or form meaning they all leave execution up to TM1 itself so I'd be surprised there would be differences as such. The only thing, hence me explicitly mentioning that in my previous message, is that indeed one needs to specify the first (optional) parameter to be able to use the forth (optional) text compare parameter. The article seems to suggest at least once, if not indirectly twice, that one can just add that forth parameter while ignoring the first one, that is not the case and might have lead to confusion.

------------------------------
Hubert Heijkers
------------------------------

Original Message:
Sent: Mon April 25, 2022 02:28 PM
From: Wim Gielis
Subject: INSTR() and MDX

Hello Hubert,

I was referring to that topic, on Instr just as this topic, since apparently there are different results in TM1 clients for the same expression.
I haven't tested it then and also not now, but I would think this will still be the case (for users in the Subset Editor and active forms).

------------------------------
Wim Gielis
------------------------------


Original Message:
Sent: Mon April 25, 2022 06:06 AM
From: Hubert Heijkers
Subject: INSTR() and MDX

Relevant in the sense of what exactly Wim?

Instr is a documented MDX function so it is not a surprise that it exists in TM1 as well I hope. The surprising part might be related to the fact that the first and the forth parameter are optional (as opposed to the last two), leaving it a bit in the middle what happens when you pass three parameters. TM1 presumes the parameters are provided in the order of the optional parameters (pretty standard for languages that don't allow specifying which parameter one is talking about) and therefore presumes if three parameters are passed it's the start index (first), text and pattern parameters. This is why all four parameters need to be specified if one wants to specify the compare type (forth) parameter as well. 

Am I missing something else Wim?

------------------------------
Hubert Heijkers


Original Message:
Sent: Sat April 23, 2022 05:54 AM
From: Wim Gielis
Subject: INSTR() and MDX

I will add a reference to this topic since it might still be relevant:
https://www.tm1forum.com/viewtopic.php?f=3&t=11598

------------------------------
Wim Gielis


Original Message:
Sent: Fri April 22, 2022 04:34 AM
From: George Tonkin
Subject: INSTR() and MDX

Thanks for the feedback Hubert and very glad to hear that I will not be Grep'ing or FindStr'ing across many instances to try and locate any MDX to be updated.

------------------------------
George Tonkin


Original Message:
Sent: Fri April 22, 2022 04:25 AM
From: Hubert Heijkers
Subject: INSTR() and MDX

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

Thanks Hubert. I will update that topic with a cross-reference to this topic.

------------------------------
Wim Gielis
------------------------------

Original Message:
Sent: Tue April 26, 2022 03:52 AM
From: Hubert Heijkers
Subject: INSTR() and MDX

Hi Wim,

None of the clients mentioned in that topic, AFAIK, interpret the MDX themselves in any way shape or form meaning they all leave execution up to TM1 itself so I'd be surprised there would be differences as such. The only thing, hence me explicitly mentioning that in my previous message, is that indeed one needs to specify the first (optional) parameter to be able to use the forth (optional) text compare parameter. The article seems to suggest at least once, if not indirectly twice, that one can just add that forth parameter while ignoring the first one, that is not the case and might have lead to confusion.

------------------------------
Hubert Heijkers
------------------------------


Original Message:
Sent: Mon April 25, 2022 02:28 PM
From: Wim Gielis
Subject: INSTR() and MDX

Hello Hubert,

I was referring to that topic, on Instr just as this topic, since apparently there are different results in TM1 clients for the same expression.
I haven't tested it then and also not now, but I would think this will still be the case (for users in the Subset Editor and active forms).

------------------------------
Wim Gielis


Original Message:
Sent: Mon April 25, 2022 06:06 AM
From: Hubert Heijkers
Subject: INSTR() and MDX

Relevant in the sense of what exactly Wim?

Instr is a documented MDX function so it is not a surprise that it exists in TM1 as well I hope. The surprising part might be related to the fact that the first and the forth parameter are optional (as opposed to the last two), leaving it a bit in the middle what happens when you pass three parameters. TM1 presumes the parameters are provided in the order of the optional parameters (pretty standard for languages that don't allow specifying which parameter one is talking about) and therefore presumes if three parameters are passed it's the start index (first), text and pattern parameters. This is why all four parameters need to be specified if one wants to specify the compare type (forth) parameter as well. 

Am I missing something else Wim?

------------------------------
Hubert Heijkers


Original Message:
Sent: Sat April 23, 2022 05:54 AM
From: Wim Gielis
Subject: INSTR() and MDX

I will add a reference to this topic since it might still be relevant:
https://www.tm1forum.com/viewtopic.php?f=3&t=11598

------------------------------
Wim Gielis


Original Message:
Sent: Fri April 22, 2022 04:34 AM
From: George Tonkin
Subject: INSTR() and MDX

Thanks for the feedback Hubert and very glad to hear that I will not be Grep'ing or FindStr'ing across many instances to try and locate any MDX to be updated.

------------------------------
George Tonkin


Original Message:
Sent: Fri April 22, 2022 04:25 AM
From: Hubert Heijkers
Subject: INSTR() and MDX

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