- Introduction to WorkXpress
- Building Your Application
- Examples and Best Practices
- Technical Manual
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.
The Substring function has three parameters, two of which are required:
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.
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.
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.
The output of the Substitute function is a string representing part of the initial string.
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 ”.