SUBST TM1 Function (like Excel MID or SQL Server SUBSTR): Use and Syntax
The SUBST function is essentially the same as the Excel MID function or the SQL Server SUBSTR function, in that it allows you to select a group of characters of a specified length from a larger string of characters.
Syntax of SUBST
The syntax is:
SUBST(string, beginning, length);
- string, is the text string that you want to take a slice out of
- beginning, is the numeric position in string where you want to start the slice
- length, is the number of characters representing the length of the slice
Usage of SUBST
An example is:
which will return ‘lor’.
Typically this will be used with a string variable as the first operator, such as:
sVariable = 'exploringtm1'; SUBST(sVariable, 4,3);
would also return ‘lor’.
It is important to note that the first operator must be a string. So if you have a source that is numeric, for example 12345 and you want to extract the last two digits, then you first need to convert this number to a string using numbertostring and then use SUBST on the result of that.
SUBST can be used in both TM1 rules and TurboIntegrator processes
Note re Excel MID and SQL Server SUBSTR
Please note that both MID and SUBSTR do not work in TM1, you must use SUBST.