Scan TM1 Function: Syntax and Use

Scan is a TM1 function that find the numeric position of the first instance of a specified substring within a string. If the substring is not found, it returns zero. It is essentially the same as the FIND command in Excel and CHARINDEX in SQL Server.

Syntax of SCAN

The syntax is:

SCAN(substring, string);

where

• substring is the substring you want to find
• string is the string you are evaluating

both substring and string can be string based variables.

Example

sSubstring = 'TM1';

sString = 'ExploringTM1';

SCAN (sSubstring, sString);

This will return 10 because the substring ‘TM1’ starts at the 10th position in ‘ExploringTM1’. Scan is really useful when combined with a subst command to find the position of a string and then extract either up to that position or from that position onwards.

The function is valid in both Rules and Turbo Integrator processes in TM1 and Planning Analytics.