String Functions for Formulas

In CRM Analytics, use string functions to create values based on other strings. For example, you can change the casing of text strings, concatenate values from multiple columns, and replace values. The arguments for string functions must be text strings or fields.

String Functions for Formulas

When you enter a formula, use the API name of the column in the expression, not the label. The parameters section is surrounded by parentheses. Also, text strings must be enclosed in single straight quotes ('This is a string.').

For example, you can nest string functions in an expression to combine the sales territory and country into a column. Then you can apply title casing to the results. title(concat(Sales_Territory, ' ',Country))

You can use the following string functions in a formula expression.

String Function	Description	Syntax
`char_length`	Returns the number of characters in the specified string. Leading and trailing whitespace characters are included in the length returned.	`char_length(field)` `field` is the text (dimension) field or string to measure. Example: A point-of-service checkout tool generates receipts with a maximum number of characters. Evaluate your product names to see their length. `char_length(product_name)`
`concat`	Returns a string by merging the values of the specified columns and input strings.	`concat(field1,field2,...fieldN)` `field1` is the first text (dimension) field or string to include in the concatenated value. `field2` is the second text (dimension) field or string to include. `fieldN` is any number of extra text (dimension) fields or strings to include. Example: To create the full name, concatenate the first and last names with a space in between them. `concat("OwnerId.FirstName",' ',"OwnerId.LastName")`
`endsWith`	Returns true if the specified string is found at the end of the column value. This function is only supported in `case` functions because it returns as a boolean.	`ends_with(fieldName,literal`) `fieldName` is the text (dimension) field to search. `literal` is the value to search for at the end of the field.
`instr`	Returns the (1-based) first location of the value 'searchString' in the specified 'field' column. Optionally, start the search at a different location with 'position', or return the location of a different searchString instance with 'occurrence'.	`instr(field, searchString, [position, [occurrence]])` `field` is the text (dimension) field or string that is being evaluated to find the `searchString`. `searchString` is the text (dimension) field or string that is being searched for in the `field`. `position` is the start location in `field` where the search begins. This parameter is optional. If you don't specify a position, the search begins at position 1. `occurrence` is the specific instance of the `searchString` to return. This parameter is optional. Use `position`, too, if you use this parameter. If you don't specify a position, the search begins at position 1. Example: Evaluate a string for an exclamation point. `instr('123!456!78!', !) --The result is 4 instr('123!456!8!', !, [4]) --The result is 7 instr('123!456!8!', !, [4, [2]]) --The result is 9`
`lower`	Returns the string with all characters in lowercase. If the input string is null, then the result is null.	`lower(field)` `field` is the text (dimension) field or string to convert to lowercase. Example: The Account.Industry column uses different casing for industries, like “Media” and “media.” To apply consistent casing to the columns values, convert them to lowercase. `lower("Account.Industry")`
`ltrim`	Removes the specified substring from the beginning of a string. If no substring is provided, remove the leading space characters from a string.	`ltrim(field,valueToBeRemoved`) `field` is the text (dimension) field or string to remove the leading spaces or specified substring from. `valueToBeRemoved` is the value to remove from the string. This parameter is optional. If you don't specify a valueToBeRemoved, the transformation removes the leading spaces. Example: Trim the leading space characters from a string. `ltrim(' 5 Spaces Before')`
`replace`	Replaces a substring with another string. If any of the arguments are null, then the function returns null. This function is case-sensitive.	`replace(field,searchString,replacementString)` `string` is the text (dimension) field or string that contains the substring to be replaced. `searchString` is the substring to replace. If searchString is an empty string, the function returns null. `replacementString` is the value that replaces the substring. Example: Change the account name from “salesforce.com” to “Salesforce” in the Account_Name column. `replace(Account_Name,'salesforce.com','Salesforce')`
`rtrim`	Removes the specified substring from the end of a string. If no substring is provided, remove the trailing space characters from the end of a string.	`rtrim(field,valueToBeRemoved`) `field` is the text (dimension) field or string to remove the trailing spaces or specified substring from. `valueToBeRemoved` is the value to remove from the string. This parameter is optional. If you don't specify a valueToBeRemoved, the transformation removes the trailing spaces. Example: Trim the 2 trailing space characters from a string. `rtrim('2 Spaces After ')`
`startsWith`	Returns true if the specified string is found at the beginning of the column value. This function is only supported in `case` functions because it returns as a boolean.	`startsWith(fieldName,literal`) `fieldName` is the text (dimension) field to search. `literal` is the value to search for at the beginning of the field.
`string`	Converts the date or number value to a string data type.	`string(expression)` `expression` is the date or number (measure) field to convert.
`substr`	Returns characters from the string, starting at the specified position and of the specified length. Also called `substring`.	`substr(field,position,len)` `field` is the text (dimension) field or string to extract the substring from. `position` is the starting character position of the substring. The first character in a string is at position 1. If position is negative, then the position is relative to the end of the string. A position of -1 denotes the last character. `len` is the length, or number of characters, to return. If len is 0, the output is an empty string. If len is negative, then the function returns null. This parameter is optional. Example: Return the 13th, 14th, and 15th character from each account ID. `substr(Account_Id,13,3)`
`title`	Returns the string with the initial character of every word in uppercase and the remaining characters in lowercase. For example, “united states” becomes “United States.”	`title(field)` `field` is the text (dimension) field or string on which to apply title casing. Example: Apply title casing on the opportunity names to ensure consistent casing on these values. `title(Opp_Name)`
`trim`	Removes the specified substring from the beginning and end of a string. If no string is specified, remove the space characters from the beginning and end of a string.	`trim(field,valueToBeRemoved)` `field` is the text (dimension) field or string to remove the specified substring from. `valueToBeRemoved` is the value removed from the string. This parameter is optional. If you don't specify a valueToBeRemoved, the transformation removes the leading and trailing spaces. Example: Remove the two leading spaces before and after a string. `trim(' Spaces Before and After ')`
`upper`	Returns the string with all characters in uppercase. If string is null, then the result is null.	`upper(field)` `field` is the text (dimension) field or string to convert to uppercase. Example: You have a column First_Name that accepts freeform entry for users to enter their first name. As a result, some names are in uppercase, some in lowercase, and others in mixed case. Convert them to all to uppercase characters to make the casing consistent. `upper(First_Name)`

Note Functions that don't handle null values explicitly don’t convert NULL values to be empty (for strings) or 0 (for numerics). For example, a concat output returns NULL if any of the concatenated fields have a null value on that row. So if the formula looks like concat(first_name, last_name), the returned data with a NULL value for last_name is:

First Name | Last Name | Output
          Alice      | Smith     | Alice Smith
          Bob        | NULL      | NULL

To handle null values, you can use the concat function with another function. For example: concat(first_name, coalesce(last_name, ''))

Notation for String Formulas
In CRM Analytics, use formulas to create custom calculations. If you manually type your formulas, use single quotes ' for string literals and double quotes " for joined fields with dot notation or reserved keywords. If you use the recipe builder tool instead of typing, the correct single or double quote is automatically used.
Reserved Keywords in Formulas
In CRM Analytics, Data Prep has reserved keywords that need special handling when manually writing formulas. Use double quotes " when referencing dataset columns that share a name with reserved keywords. To avoid cross-referencing your column names to this list, the best practice is to use double quotes around all column names.

Did this article solve your issue?

Let us know so we can improve!

String Functions for Formulas