Scan TM1 Function: How to Use, Syntax and Examples

Scan is a TM1 function that finds 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 of SCAN

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.

Further Example with SCAN, IF and SUBST in a TI Process

This example will look for _p in the variable vCustomer (Customer ID) and if it exists, strip the _p from the ID. It uses the IF, SCAN and SUBST functions:

# Strip _p from Customer ID
IF ( SCAN ( '_p', vCustomer ) = 0);
    vCustomer = vCustomer;
ELSE;
    vCustomer = SUBST ( vCustomer, 1, SCAN ( '_p', vCustomer )-1 );
ENDIF;