Substring (Expression Function)

Purpose

The Substring function (SUBSTRING) is used obtian a certain portion of a string by defining the starting character (by its index within the larger string) and the length of the desired string.

Parameters

The Substring function has three parameters, two of which are required:

Value

Allowed Inputs: field value or text string

The first required parameter is the string that the substring will be extracted from. Either use the use the Query Builder to choose a field that contains the value or use type a value to enter a text string. While it is technically possible to use a text string with this function it is not recommended.

Start Position

Allowed Inputs: field value or a number

The second required parameter of the Substring function is the index of the character starting from the left side of the string. The 0 index is the first character of a string. Negative numbers are also allowed. When a negative number is used as the start position the substring will start from the right of the string. The -1 index is the last character of a string.

Length

Allowed Inputs: field value or a number

The third parameter, which is optional, it is the length of the substring to be extracted. If this parameter is omitted then the substring will continue from left to right to the end of the string. Negative numbers are also allowed. When a negative number is used as the length then the substring will continue up to that index from the right side of the string. A length of -1 would continue to but not include the last character of a string.

Output

The output of the Substitute function is a string representing part of the initial string.

Example

Several common uses for this function are pulling the first character from a string, pulling all but the last character from a string, or truncating a string to a fixed length. To pull the first character from a string the function SUBSTRING(“This is a Test!”,0,1) will result in “T”. To pull all but the last character from a string the function SUBSTRING(“This is a Test!”,0,-1) will result in “This is a Test”. To make sure that a string is always 5 characters long starting from the left then SUBSTRING(“This is a Test!”, 0, 5) will result in “This ”.

expression function - substring.txt · Last modified: 2016/09/14 14:19 (external edit)
Copyright WorkXpress, 2022