Built-in Functions → String Functions
The string functions convert, truncate, and search within text strings. Here is a list of supported string functions:
Functions for Finding and Replacing Strings
Function | Signature | Description |
---|---|---|
find() | find(string exp1, string exp2) | Returns the index value of the first occurrence of a string within another string |
findLast() | findLast(string exp1, string exp2) | Returns the index of the last substring match |
subString() | substring(string exp, int exp) | Returns a substring from a string expression |
subString() | substring(string exp, int exp, int exp) | Returns a substring from a string expression with a specified length |
splitPart() | splitPart(string value, string delimiters,int findNum) | 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 | Signature | Description |
---|---|---|
lower() | lower(string exp) | Returns its string argument in all lower case |
upper() | upper(string exp) | Returns its string argument in all upper case |
Functions for Trimming Whitespace from Strings
Function | Signature | Description |
---|---|---|
lTrim() | lTrim(string value) | Returns its string argument without any leading white space |
rTrim() | rTrim(string str) | Returns its string argument without any trailing whitespace |
trim() | trim(string exp) | Returns its string argument without leading or trailing whitespace |
Additional String Related Functions
Function | Signature | Description |
---|---|---|
ifNull() | ifNull(exp1, exp2) | Returns one of two expressions depending on whether one of the expressions is NULL |
length() | length(string exp) | Returns the length of a string |
bin() | bin(double exp, string c1, double d1,...,string default)) | Splits data values based on specified criteria |
concat() | concat(string exp, string exp, ...) | Returns the concatenation of an arbitrary number of string expressions |
repeat() | repeat(string value, int count) | Returns a string repeated several times |
reverse() | reverse(string value) | Returns a reversed string value |
Additional Considerations
Backslash support as an escape character
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.