String Functions

Functions for Finding and Replacing Strings

Function Syntax Description
find() find(string expr field, string find) Returns the index value of the first occurrence of a string within another string
findLast() findLast(string expr field, string find) Returns the index of the last substring match
substring() substring(string expr field, int start, [int end]) Returns a substring from a string expression
splitPart() splitPart(string delimited_string, string delimiters, int index) Returns an element of a delimited string
replace() replace(string field, string old, string new) Returns a string with old substrings replaced by new substrings

Functions for Transforming Strings

Function Syntax Description
lower() lower(string expr str) Returns its string argument in all lower case
upper() upper(string expr str) Returns its string argument in all upper case

Functions for Trimming Whitespace from Strings

Function Syntax Description
lTrim() lTrim(string str) Returns its string argument without any leading white space
rTrim() rTrim(string str) Returns its string argument without any trailing whitespace
trim() trim(string str) Returns its string argument without leading or trailing whitespace
Function Syntax Description
ifNull() ifNull(string expr str1, string expr str2) Returns one of two expressions depending on whether one of the expressions is NULL
length() length(string expr str) Returns the length of a string
bin() bin(series, label_a, limit, label_b) Splits data values based on specified criteria
concat() concat(string str1, string str2, . . ., string strN) Returns the concatenation of an arbitrary number of string expressions
repeat() repeat(string str, int rep) Returns str repeated rep times
reverse() reverse(string str) Returns str reversed

Additional Considerations

Backslash support as an escape character

Staring with the 2021.4.3 release, the Engine supports using the backslash mark \ as an escape character that you can use within a string in a formula to treat the next character as literal text. For strings that you enclose in double quotes and include double quotation marks within, you must precede each quotation mark that you want to include in the string with a backslash \. The same applies to single quotation marks within a string enclosed in single quotes. In addition, to include a backslash in a string, precede it with another backslash as an escape character.

For example:

The formula:

concat(
	"Place a string between two double quotes: \"TEXT\"",
	' or two single quotes: \'TEXT\'.',
	"Use a backslash \\ as an escape character."
)

The Result:

Place a string between two double quotes: "TEXT" or two single quotes: 'TEXT'. Use a backslash \ as an escape character.