The SUBSTR function selects a portion of a specified string (text) to display beginning at a selected distance from the left of the string (count) for as many characters are specified (length) into the string. The length parameter is optional. If length is not supplied, the function returns all remaining characters. This function is only applicable for alphanumeric cells.
Syntax
SUBSTR(text, count, <length>)
-
Example 1
SUBSTR("Prince Edward Island",8)
Where 8 characters into the string is the E of Edward. Therefore, returns the value "Edward Island".
-
Example 2
SUBSTR("Prince Edward Island",8, 6)
Where 8 characters into the string is the E of Edward and 6 characters from the E of Edward is the last D of Edward. Therefore , returns the value "Edward".
Database Filter Syntax
SUBSTR(DATABASE -> FIELD, number), OPERATOR VALUE
-
Example 1
SUBSTR(GL->JE_DESC,20,4)="BANK"
Would filter for all journal entries that have, and match exactly, the four letters "BANK." at the 20th position counted from the left side in the journal description.
-
Example 2
SUBSTR(LTRIM(AM->AC_NO),5,2)="22"
Would filter all accounts with account numbers that have "22" in the 5th position from the left.
For using dates in a filter, see Date Fields for more information.
